Gossamer Forum
Quote Reply
Query
I have column LastName in my database ( MSSQL2000).
How can I retrieve records containing LastName where 3 letters of this column the same ( does not matter what this letters are )
Example:

Aaabbbbb
Aaaccccc
Aaabbbbb
Bbbcccc
Bbbdnnnn

Thank you
Quote Reply
Re: [LisaLisa] Query In reply to
Hmm, I don't think you can do this in SQL. You'll need to write a stored procedure, or evaluate the data in another language (perl, vb, etc).

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Query In reply to
Thank you Alex

May be you can give an example of a stored procedure for this case

Thank you again
Quote Reply
Re: [LisaLisa] Query In reply to
Sorry, I don't know enough about MS SQL for this..

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Query In reply to
Thank you Alex
Quote Reply
Re: [LisaLisa] Query In reply to
I do not have MS Sql server but I tried the problem in MySql , just for fun.

Using your above data :

SELECT * from table where lastname REGEXP '^.+[a,b,c]{3}$'

finds all ending in exactly 3 of a or b or c . [a-zA-Z] does not work though !

I know other than the % used with a like operator there was an _ underscore wildcard

The underscore was supposed to match one character. I do not know if MSSQL 2000

supports the underscore character as a wild card.

I just found a microsoft article that might allow you to use the underscore as any character:

but this will not help as it can be "abc".

So with a lot of typing you should be able to do in MSSQL :

select * from table where lastname like '%aaa' or lastname like '%bbb' or lastname like '%ccc' .......

until you get to : or lastname like '%zzz' [ case sensitive ? ].


Hope this helps.

Thanks

Kode

Last edited by:

kode: Aug 11, 2002, 12:09 PM