Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Searching for case sensitive data Expand / Collapse
Author
Message
Posted Wednesday, April 15, 2009 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, 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
Post #697897
Posted Wednesday, April 15, 2009 2:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #697904
Posted Wednesday, April 15, 2009 2:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, 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%"?


Post #697951
Posted Wednesday, April 15, 2009 3:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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
Post #697959
Posted Wednesday, April 15, 2009 4:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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!!

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? -- Stephen Stills
Post #698018
Posted Wednesday, April 15, 2009 5:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
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!!


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



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
Post #698059
Posted Wednesday, April 15, 2009 6:23 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #698071
Posted Thursday, April 16, 2009 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, 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
Post #698351
Posted Thursday, April 16, 2009 6:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #698361
Posted Thursday, April 16, 2009 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 10,278, Visits: 13,262
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

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
Post #698404
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse