question

SQLserver_novice avatar image
SQLserver_novice asked

Search for Characters in SQL server 2000 or 2005

Please guide me below mentioned problem

String1 = ÁĚĎ String2 = PQĎ

I need to find out whether any of the characters in string1 appears in string2. My fuction should exit the loop at the first occurance of any of the string1 character in string2. I do not want to process each of the character in string1 in a loop as i have to process millions of records in the database. Can anyone guide me what sql statement can be written in sql server 2000 or sql server 2005.

With Regards

Novice

sql-server-2005t-sqlsql-server-2000
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered 
SELECT PATINDEX('%[abc]%', 'This is a test')

That will return 9 - the 1-based index of the first occurrance of any of the letters a, b or c

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark Allison avatar image
Mark Allison answered

Have you thought of using full-text indexing and using the CONTAINS function?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

I know it's an old post, Ben, but did either of these answers solve your problem?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.