In SQL, is there a way to use a where clause to pull only data that has characters in the string?

Question

I want to limit my search based on an char column. The values stored in this column are alphanumeric. All values begin with a number, but some end with a letter. I want to select only the ones that end in a letter. Do you know a way to select on this criteria?

Answer 1

Use the right(column,1) function on the string to get the last character, and then wrap it with an ASCII() function to get the ascii value of the character. ASCII codes for characters are (decimal) 65-90 for uppercase and (decimal) 97-122 for lower case.



So the TSQL code would be

ascii(right(columnname,1))

Answer 2

Yes, you would have to break apart the string with a binary tree I would say, and then test the characters in that column against the characters contained in the string.

Make sure to store the string's characters into an array.

Technically, you don't even have to do that, since a string is already a character array, so if you know how to use string commands, you can just look through the string's character array.

Answer 3

You can do that with a regular expression. Depending on the version of SQL you use, how you write the regular expression varies.



The regular expression .*[a-zA-Z]$ evaluates as "find any string that ends with an a or z, case-insensitive."



In MySQL:



SELECT *

FROM table

WHERE column REGEXP '.*[a-zA-Z]$'



In Transact-SQL:



SELECT *

FROM table

WHERE column LIKE '%[a-zA-Z]'

Answer 4

dhvrm is correct. The other two answers, along with mine, are worthless. I am primaily answering so I will remember to go back and vote for dvhrm's answer later.