SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching for case sensitive data


Searching for case sensitive data

Author
Message
rons-605185
rons-605185
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 162
SQL newbie using MS SQL 2005.
Have customer order table with customer order numbers that are prefixed with "CQS" followed by a 7 digit number.
Example: CQS0002537.
Somehow users managed to input some of the customer order numbers as lower case "cqs0002536" instead of the correct upper case CQSxxxxxx.

Question: what is the syntax of a sql script/query to search the co_num column in the customers table to show just
the data that is "cqs%" but not return "CQS%".

Would also like to know how to do the same for the inverse (show only the upper case "CQS" but not the
lower case "cqs").

I tried the Upper and Lower functions but it showed all results in either upper/lower case - it did not show the data as it really exists.

Looking for best method to search for case sensitive data.

Thanks.

Rons
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3527 Visits: 3934
Hi

The answer depends on your future requirements. If it is just a temporary failure you can use COLLATE within the WHERE clause to specify the column as case-sensitive. If this is/becomes a standard requirement you should set the collation of the column to case-sensitive.

Here a little sample for both approaches:

DECLARE @t TABLE
(
Id INT,
CS_text VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS,
CI_text VARCHAR(30)
)

INSERT INTO @t
SELECT 1, 'hello', 'world'
UNION ALL SELECT 2, 'Hello', 'World'

SELECT *
FROM @t
WHERE CS_text = 'Hello'

SELECT *
FROM @t
WHERE CI_text COLLATE SQL_Latin1_General_CP1_CS_AS = 'World'



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
rons-605185
rons-605185
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 162
Data in co_num column of customer table shows:

CQS0002509
CQS0002510
CQS0002511
cqs0002512
cqs0002513
cqs0002514
cqs0002515
CQS0002516
CQS0002517
CQS0002518
CQS0002519
CQS0002520

Tried the select statement as follows:

SELECT *
FROM co
WHERE co_num COLLATE SQL_Latin1_General_CP1_CS_AS like 'cqs%'

Results:

co_num
----------
cqs0002512
cqs0002513
cqs0002514
cqs0002515

which is exactly what I want :-)

Now I need to fix the data using the following Update command

update co
set co_num = 'cqs0002515'
where co_num = 'CQS0002515'

Is there a way to update a group of records such as all "cqs%" to become "CQS%"?
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3527 Visits: 3934
Hi

What about UPPER? ;-)


DECLARE @t TABLE
(
txt VARCHAR(30)
)

INSERT INTO @T
SELECT 'CQS0002509'
UNION ALL SELECT 'CQS0002510'
UNION ALL SELECT 'CQS0002511'
UNION ALL SELECT 'cqs0002512'
UNION ALL SELECT 'cqs0002513'
UNION ALL SELECT 'cqs0002514'
UNION ALL SELECT 'cqs0002515'
UNION ALL SELECT 'CQS0002516'
UNION ALL SELECT 'CQS0002517'
UNION ALL SELECT 'CQS0002518'
UNION ALL SELECT 'CQS0002519'
UNION ALL SELECT 'CQS0002520'

UPDATE t SET txt = UPPER(txt)
FROM @t t
WHERE txt COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'

SELECT * FROM @t



Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5442 Visits: 6900
Is there a way to update a group of records such as all "cqs%" to become "CQS%"?


Keeping it simple....


UPDATE sometable
SET someColumn = REPLACE(someColumn,'cqs','CQS')
WHERE somecolumn LIKE 'cqs%'



Sorry to butt in, Flo, but you're answering ALL the questions!! :-P

Now for the time trial results. You won the first round because of the COLLATE in the WHERE clause. That was shrewd because it changed ONLY those rows with a lower case 'cqs'. I would still avoid doing that as a matter of practice, because there might be some mixed case characters lurking in there as well.

However, it seems that the REPLACE function is about 10% faster than the UPPER function. I assume that it was quicker to test every character to see if it was a 'c' (and replace only three) faster than it was to run the UPPER conversion on every character.

By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations! :-)



;with Tally (N) AS (SELECT TOP 10000 ROW_NUMBER() over (order by sc1.id) FROM Master.dbo.SysColumns sc1)
select 'cqs'+LEFT('00000'+CAST(N as varchar(10)),10) as keyno
into #testTable
from tally
create unique clustered index #pk_TestTable on #testTable ( keyno )


update #testTable
set Keyno = UPPER(keyNo)
where keyNo like '%1%'

set statistics time on;

print '--Flo'
UPDATE #testTable
SET keyno = UPPER(keyNo)
FROM #testTable t
WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'
set statistics time off;

-- reset
update #testTable
set Keyno = lower(keyNo)
where keyNo not like '%1%'

set statistics time on;
print '--Bob'
UPDATE #testTable
SET keyno = REPLACE(keyNo,'cqs','CQS')
WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'


set statistics time off;

drop table #testTable



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3527 Visits: 3934
Hi Bob!

Bob Hovious (4/15/2009)
Is there a way to update a group of records such as all "cqs%" to become "CQS%"?


Keeping it simple....


UPDATE sometable
SET someColumn = REPLACE(someColumn,'cqs','CQS')
WHERE somecolumn LIKE 'cqs%'



Now for the time trial results. You won the first round because of the COLLATE in the WHERE clause. That was shrewd because it changed ONLY those rows with a lower case 'cqs'. I would still avoid doing that as a matter of practice, because there might be some mixed case characters lurking in there as well.

However, it seems that the REPLACE function is about 10% faster than the UPPER function. I assume that it was quicker to test every character to see if it was a 'c' (and replace only three) faster than it was to run the UPPER conversion on every character.


Just for fun, a different solution. The test results a bit strange. Sometimes the STUFF seems to be faster than the REPLACE and sometimes other way around. I also tested 'CQS' + SUBSTRING... but seems to be slower. Same with RIGHT...

--- reset
update #testTable
set Keyno = lower(keyNo)
where keyNo not like '%1%'

set statistics time on;
print '--Flo 2'
UPDATE #testTable
SET keyno = STUFF(keyNo, 1, 3, 'CQS')
WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'
set statistics time off;




Sorry to butt in, Flo, but you're answering ALL the questions!! :-P

:-D
Sorry for that, I've not been online the most of the day and just have been a bore.
:-D


By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations! :-)


Yes, it has been a really great discussion! I never saw so much input by so many professionals in any other thread. I'm still doin' some many tests, also for some other CLR approaches. When I'm done I'll share it with you!

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5442 Visits: 6900
Just share it with the entire thread and I'll read it.

Like Jeff, I'm now thinking that I will have to learn to code CLR in self defense. ;-)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
rons-605185
rons-605185
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 162
Thanks Flo and Bob:

The Collate statement allowed me to find the incorrect string and the Update query allowed me to correct the data

UPDATE sometable
SET someColumn = REPLACE(someColumn,'cqs','CQS')
WHERE somecolumn LIKE 'cqs%'


Thanks again:-)
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5442 Visits: 6900
You're welcome, Ron. Hope we didn't overwhelm you with detail. ;-)

These sidebar discussions often go into way more minutiae than is required to solve your problem, but sometimes they dredge up some really good information. For one-shot fixes, you may not care whether you fix it with UPPER, REPLACE, STUFF, 'CQS'+SUBSTRING(), or 'CQS'+RIGHT(). But when you get to coding transactions with high volumes, it might be a good thing to know which functions are easier on the CPU. Best of luck to you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18622 Visits: 14899
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search