Regular Expression Problem
-
I am trying to create a query to look for any expression that contains a lowercase character in the description.
SELECT * FROM Table1 WHERE Description LIKE '%[a-z]'
This is still returning all rows that do not contain any lowercase characters.
Outside of creating a CLR or additional functions, does anyone have any idea why this wouldn't be working?
Thanks
-
That requires a change in "COLLATION"... unfortunately, there's a bug in SQL Server that destroys the ability to use collation where a range of letters is included in a LIKE... soooo... the following will NOT work...
SELECT * FROM Table1 WHERE Description LIKE '%[a-z]' COLLATE SQL_Latin1_General_CP850_CS_AS
... but this will... (it's just a bit slower)....
DECLARE @Find VARCHAR(10)
SET @Find = '%[abcdefghijklmnopqrstuvwxyz]' COLLATE SQL_Latin1_General_CP850_CS_AS
SELECT * FROM Table1 WHERE Description LIKE @Find
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you for the response, but I am still getting the same results. What I need to do is to pull only those Descriptions that contain a lower case character, I am still pulling records that are all caps.
What we are trying to do is to locate those records that have a description that was possibly modified outside of our legacy system. Since our legacy system sends us our records in CAPS we need to locate those that contain the lower case characters. I know this is not a 100% solution to locating those records that were modified, but it gives us something to go back to the business with as far as data integrity is concerned.
Thanks again for the response....
-
You mean the second example I posted didn't work? (The first example was how NOT to do it)...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Correct, I tried it out and it returned the same results I was getting before. I copied and pasted your suggestion.
Thank you for your assistance....
-
maybe try ...
SELECT * FROM Table1
WHERE Description COLLATE SQL_Latin1_General_CP850_CS_AS
LIKE '%[a-z]' COLLATE SQL_Latin1_General_CP850_CS_AS
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I am still receiving the same results, it is still returning rows that do not contain any lower case characters.
Thanks for the assiatance.....
-
Crud... I can't make COLLATION work anyway shape or form with LIKE (thought I did but was wrong)... it only works with an equates...
This one works though... guaranteed...
SELECT *
FROM Bigtest
WHERE CAST(SomeValue AS VARBINARY(8000))
<> CAST(UPPER(SomeValue) AS VARBINARY(8000))
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Problem solved... ALZDBA reminded me of the other collation...
Both of these work (did positive testing instead of negative testing this time, my bad)...
DECLARE @FIND VARCHAR(8000)
SET @FIND = '%[abcdefghijklmnopqrstuvwxyz]%' COLLATE SQL_Latin1_General_CP850_CS_AS
SELECT *
FROM Table1
WHERE Description COLLATE SQL_Latin1_General_CP850_CS_AS
LIKE @FIND
SELECT *
FROM Table1
WHERE Description COLLATE SQL_Latin1_General_CP850_CS_AS
LIKE '%[abcdefghijklmnopqrstuvwxyz]%' COLLATE SQL_Latin1_General_CP850_CS_AS
The key is that as you as you use a range like [a-z], the collation on the like just seems to loose its mind in SQL Server 2000... you must explicity mention every character, instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It worked like a charm.
Thank you for all your help, I greatly appreciate it.....
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply