SQL Char Search 1

[sa0309]

I need to search a Last_name field that contain suffix's. The suffix always follows that Last_name with a space in between.

Examples:

Last_name
smith II
smith IV
smith jr
smith sr

I've tried:

select Last_name
from TABLE1
where Last_name like '% [A-Za-z]'


Any assistance would be appreciated.
Thank you.

 

[gmmastros]

That's not working because the square brackets mean "Look at a single character". Since your suffixes are 2 characters, they do not match the pattern. Basically, your pattern says, "find rows where the 2nd to last character is a space and the last character is a letter (a through z).

There are several ways to change the pattern. Some may be better than others depending on your data. Ex:

Declare @Temp Table(Last_Name VarChar(50))

Insert Into @Temp Values('smith I')
Insert Into @Temp Values('smith II')
Insert Into @Temp Values('smith IV')
Insert Into @Temp Values('smith jr')
Insert Into @Temp Values('smith sr')
Insert Into @Temp Values('smith')

select Last_name
from @Temp
where Last_name like '% [A-Za-z][!]%[/!]'

select Last_name
from @Temp
where Last_name like '% [A-Za-z][!][A-Za-z][/!]'

Notice the sample data I added, and the patterns I am using. Copy/paste this code to a query window and run it to see the differences.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

George, your first example is the better of the two...at least for since I am a third.... Johnson III. Your second example would still eliminate me from the results...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[gmmastros]

Robert,

I was worried about people with 2 last names but no suffix similar to people with hyphenated last names. This is exactly why I showed 2 variations of the query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sa0309]

These variations worked great. That's exactly what I was after.

Thank you.

 

[SgtJarrow]

You deserve a star for thinking of us chosen few.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer