SQL question

Deebaser

Good morning gents,

I'm trying to write a query to identify the following

LIKE ('ABx%') where 'x' is another letter. Basically, I'm attempting to distinguish instances where the third character is a letter, not a number in an alpha string that begins with "AB" without resorting to

column LIKE ('AB%')
AND
( column NOT LIKE ('AB0%') OR
column NOT LIKE ('AB1%') OR
column NOT LIKE ('AB2%') OR
column NOT LIKE ('AB3%') OR
column NOT LIKE ('AB4%') OR
column NOT LIKE ('AB5%') OR
column NOT LIKE ('AB6%') OR
column NOT LIKE ('AB7%') OR
column NOT LIKE ('AB8%') OR
column NOT LIKE ('AB9%') )

Jimmy King

Which db server are you working with? Does it have regular expression capabilities like MySQL? I have no idea how common that is to be supported by databases since my only real experience is with MySQL.

If this is a large table and going to be used frequently, I probably wouldn't use that functionality. For a one time thing or something just done on occasion or a very small table, it's probably the quickest way of doing it.

Lewisham

Google:
[A-Za-z]

match uppercase and lowercase letters

so try
like ('AB[A-Za-z]%')

Aumni

Not IsNumeric(Mid(Column,3, 1))

I think.

Or Not (Mid(Column,3,1)) between 0 and 9

Depends on the DB like mentioned above.

Lewisham

Jimmy King wrote: »

Which db server are you working with? Does it have regular expression capabilities like MySQL? I have no idea how common that is to be supported by databases since my only real experience is with MySQL.

If this is a large table and going to be used frequently, I probably wouldn't use that functionality. For a one time thing or something just done on occasion or a very small table, it's probably the quickest way of doing it.

Doing like is slow, but it depends on the rest of the query (if the rest of the query throws away all but 40 rows, it's fine) and also on the indexing of the table. Some DB packages can optimize a regex on a column too.

Sounds like the OP doesn't need to worry about premature optimization right now.

Deebaser

Im pulling the data from a Sybase sever that is pretty much MS SQL. I probably should have stated that from the get go. MID and [A-Za-z] aren't supported unfortunately.

Aumni

IsNumeric(SubString(Column,3,1))
?

Lewisham

http://iablog.sybase.com/paulley/2009/06/using-regular-expressions-with-sql-anywhere/ ?

exmello

Doing LIKE on the beginning of an indexed value is not necessarily slow. i.e. LIKE '%ebase%' performs differently than LIKE 'Dee%'

vonPoonBurGer

In terms of ANSI-standard pure SQL, the query in the OP is the best you're going to do. For a cleaner query you're going to need DB-specific functionality, either better pattern matching (like MySQL's regex capabilities) or a string function that lets you test the third character to see if it's an integer.

In SyBase, which I think uses Transact-SQL, the only out-of-the-box method I can think of is this one:

SELECT * FROM table WHERE column LIKE 'AB%' AND ascii(substring(column,3,1)) NOT BETWEEN 48 AND 57;

Basically, find the rows with an "AB" prefix, convert the third character to its ASCII code value and filter out any row where that value is between 48 and 57. Those are the ASCII codes for digits 0 through 9 respectively.