Get string data in SQL server
This doesn't really answer your question, it certainly doesn't add anything to Marc's existing answer in terms of solving your real problem, it just means demonstrating how easy it is to fix your design (this whole script runs in about a second on my local express instance of SQL Server 2012).
CREATE TABLE dbo.T
(
ThreePartData VARCHAR(60)
);
-- INSERT 20,000 ROWS
INSERT dbo.T (ThreePartData)
SELECT t.ThreePartName
FROM (VALUES ('john;144;ny;'), ('Nelson;154;NY;'), ('john;144;NC;'), ('john;144;kw;')) t (ThreePartName)
CROSS JOIN
( SELECT TOP (5000) Number = 1
FROM sys.all_objects a
CROSS APPLY sys.all_objects b
) n;
GO
-- HERE IS WHERE THE CHANGES START
/**********************************************************************/
-- ADD A COLUMN FOR EACH COMPONENT
ALTER TABLE dbo.T ADD PartOne VARCHAR(20),
PartTwo VARCHAR(20),
PartThree VARCHAR(20);
GO
-- UPDATE THE PARTS WITH THEIR CORRESPONDING COMPONENT
UPDATE dbo.T
SET PartOne = PARSENAME(REPLACE(ThreePartData, ';', '.') + 't', 4),
PartTwo = PARSENAME(REPLACE(ThreePartData, ';', '.') + 't', 3),
PartThree = PARSENAME(REPLACE(ThreePartData, ';', '.') + 't', 2);
GO
-- GET RID OF CURRENT COLUMN
ALTER TABLE dbo.T DROP COLUMN ThreePartData;
GO
-- CREATE A NEW COMPUTED COLUMN THAT REBUILDS THE CONCATENATED STRING
ALTER TABLE dbo.T ADD ThreePartData AS CONCAT(PartOne, ';', PartTwo, ';', PartThree, ';');
GO
-- OR FOR VERSIONS BEFORE 2012
--ALTER TABLE dbo.T ADD ThreePartData AS PartOne + ';' + PartTwo + ';' + PartThree + ';';
Then your query will be as simple as:
SELECT *
FROM T
WHERE LOWER(PartThree) = PartThree COLLATE Latin1_General_CS_AS;
And since you recreated the computed column with the same name, any select statements used will not be affected, although updates and inserts will need to be addressed.
source to share
Force-match against case, and then compare force-lowercase with the original:
SELECT ...
FROM ..
WHERE LOWER(name) = name COLLATE Latin1_General_CS_AS
^^---case sensitive
If the name started with zero, then LOWER () will not change it and you will get a match. If it is something like John
, then you will do john = John
and case sensitivity will fail.
source to share
Using BINARY_CHECKSUM
, we can get strings with lowercase or uppercase
CREATE TABLE #test
(
NAME VARCHAR(50)
)
INSERT INTO #test
VALUES ('john;144;ny;'),
('Nelson;154;NY;'),
('john;144;NC;'),
('john;144;kw;')
SELECT *
FROM #test
WHERE Binary_checksum(NAME) = Binary_checksum(Lower(NAME))
OUTPUT
name
-----------
john;144;ny;
john;144;kw;
source to share