SQLTeam.com | Weblogs | Forums

SQL help for string with similar names


#1

Hi Peeps,

I have a column to be filtered out based on string "ALLTRACK"

Select * from tablename
where column like '%alltrack%'

There is also a column name called 'GolfAlltrack', so the query becomes

select * from tablename
where column like '%alltrack%'
and column not like '%golfalltrack%'

Now, the column is combination of multiple string so the column can have one of below strings.

ALLTRACK,GOLFALLTRACK
ALLTRACK,GOLFALLTRACK,Beetle
ALLTRACK,GOLFWGN,GOLFALLTRACK

how can we only have data for the rows that have a column only like 'ALLtrack', column not having 'GOLFalltrack' and including the count for examples above where we have both in as a same column?

please advise.

thanks,
PP


#2

It would be better that you don't have this attribute-data as a comma delimited list concatenated to make the value for a single column. If it was me I would have a separate table with a Key/Value column pair - the KEY would be the ID of the parent record, and the VALUE would be the attribute ALLTRACK or GOLFALLTRACK.

If you wanted to you could enforce validation on the VALUE - i.e. only Values that exist in some other table of ValidValues. At the very least you could report on the frequency of the actual values used, and do something about the ones that appear very infrequently (which are likely to be wrong-spelling or synonyms)

In the mean time you could use a SPLITER function that would separate the values in your column so that you could treat them as a pseudo-child table, and then make your test (based on EQUALS rather than LIKE).

If your search for LIKE '%alltrack%' is ONLY intended to match the whole-word ALLTRACK and provided that all your data is carefully comma delimited - with no spaces or other separating characters - then you could do

where ',' + column + ',' like '%,alltrack,%'
and ',' + column + ',' not like '%,golfalltrack,%'

but performance will be dreadful as SQL will not use any suitable indexes. If you only have a few thousand rows you won't notice, but it will scale very badly.


#3

Perhaps more broadly you need the main condition to be:

select * from tablename
where column like '%[^a-z]alltrack%'

That is, find "alltrack" in the column, but ignore it if is preceded by a letter: falltrack, halltrack, etc, would be ignored.


#4

I think you'd need to ensure there was a prefix on the Column (and perhaps a suffix too), e.g.

where ',' + column like '%[^a-z]alltrack%'
or 
where ',' + column + ','  like '%[^a-z]alltrack[^a-z]%'

that would also work for any other, bogus, separating symbols, or spaces, which might exist in the data (assuming that pure comma-separated is not guaranteed)

Its going to run like treacle though :frowning:


#5

thanks guys- it solved my puzzle