CODEWITHSUNDEEP
sql-server
Abhiram
Abhiram

Reputation: 65

get lowercase data in SQL server

I have data like below in one of the column in table.

john;144;ny;
Nelson;154;NY;
john;144;NC;
john;144;kw;

I want to retrieve the rows which has lowercase in 3rd part of the data so i need to get 

john;144;kw;
john;144;ny;

is possible to get the data like this?

Upvotes: 3

Views: 288

Answers (3)

GarethD
GarethD

Reputation: 69809

This does not really answer your question, it certainly adds nothing to Marc's existing answer in terms of resolving your actual problem, it is merely meant as a demonstration of how simple it is to correct 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 is as simple as:

SELECT  *
FROM    T
WHERE LOWER(PartThree) = PartThree  COLLATE Latin1_General_CS_AS;

And since you have recreated a computed column with the same name, any select statements in use will not be affected, although updates and inserts will need addressing.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93754

Using BINARY_CHECKSUM we can retrieve the rows which has lowercase or upper case

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;

Upvotes: 0

Marc B
Marc B

Reputation: 360842

Force a case-sensitive matching, and then compare forced-lowercase to original:

SELECT ...
FROM ..
WHERE LOWER(name) = name  COLLATE Latin1_General_CS_AS
                                                 ^^---case sensitive

If the name is all-lower to start with, then LOWER() won't change it, and you'll get a match. If it's something like John, then you'd be doing john = John and the case-sensitivity will fail the match.

Upvotes: 5

Related Questions