January 20, 2016 at 8:31 am
Select * FROM tableA where desc like 'Unknown%';
How do I modify this so that I get only hits that have the desc starting exactily
with the word "Unknown...." and not "UNKNOWN..."
January 20, 2016 at 8:39 am
SELECT *
FROM dbo.CaseSensitiveTest
WHERE Value1 LIKE '%Test%' Collate SQL_Latin1_General_CP1_CS_AS
GO
The above is fine.. But can someone help me with the following: How do I do this search to be case sensitive ?
SELECT p.name, m.definition, charindex('''Unknown''', m.definition, 1 )
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
ORDER BY 3 desc
January 20, 2016 at 8:42 am
Sorry, found the answer....
The one below works fine!
SELECT p.name, m.definition, charindex('''Unknown''', m.definition, 1 )
FROM sys.procedures p
JOIN sys.sql_modules m ON p.object_id = m.object_id
where
m.definition LIKE '%Unknown%' Collate SQL_Latin1_General_CP1_CS_AS
ORDER BY 3 desc
January 20, 2016 at 8:44 am
You just define the collation the same way.
These are examples and shouldn't be used as real filters.
CREATE TABLE tableA(description varchar(100));
INSERT INTO tableA
VALUES( 'Unknown'),( 'UNKNOWN'),( 'unknown'),( 'Unknówn');
Select *
FROM tableA
where charindex('Unknown', description COLLATE Latin1_General_Bin, 1 ) > 0;
Select *
FROM tableA
where charindex('Unknown', description COLLATE Latin1_General_CS_AI, 1 ) > 0;
Select *
FROM tableA
WHERE charindex('Unknown', description COLLATE Latin1_General_CS_AS, 1 ) > 0;
GO
DROP TABLE tableA;
January 20, 2016 at 9:29 am
So which one should I use ( out of the 3 )
January 20, 2016 at 9:32 am
I see, all 3 work equally well! ( I ran your code )
I am not sure what all those stand for ? Can you recommend which I should be using ( Any preference )
January 20, 2016 at 9:42 am
They don't work equally.
The first one uses a binary collation which is the fastest as it will just compare equal bytes.
The second one is Case Sensitive (CS) and Accent Insensitive (AI) this is the only one that returns 2 rows.
The third one is Case Sensitive (CS) and Accent Sensitive (AS) this will check for case and accents when making comparisons.
For performance, you could use the binary. For exact requirements, you could use one of the others or even a different one. Find more information on BOL: https://msdn.microsoft.com/en-us/library/ms143515(v=sql.105).aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply