CODEWITHSUNDEEP
sql
Vipin Singhal
Vipin Singhal

Reputation: 53

remove numeric values in data using sql

my data is :

Id       Case ID#
1.  QCCR1A105369, QCCR1A104986 ,QCCR1A103717
2.  QCIM1A106902,4613410733 QCIM1A106902; 4613344523 QCIM1A105842; 4614004212 QCIM1A106580; 4614060189 QCIM1A106676
3.  QCCR1D93616, QCCR1D92488, QCCR1D58461
4.  QCCR1B40216 .... ,QCCR1B39080, QCCR1B40216, QCCR1B39745, QCCR1B38463 , QCCR1B38618, QCCR1B38619, QCCR1B38620, QCCR1B38621, QCCR1B38622, QCCR1B38465, QCCR1B38623
5.  QCCR2A30221 QCCR2A30223 QCCR2A30222 QCIM2A30416

My output will be Id 1,3,4,5. I want only that rows, which have starting value QC not any numeric value. For ID 2 you can see there are some numeric values, please tell me how can I achieve it.

Upvotes: 0

Views: 121

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

You could use a table valued function to split your value by a delimiter like this:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @ItemTable TABLE (Item VARCHAR(250))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @ItemTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

Then this query returns the expected result:

SELECT t.*
FROM dbo.TableName t
WHERE NOT EXISTS
(
   SELECT 1 FROM dbo.Split(t.CaseID, ',') split
   WHERE NOT LEFT(LTRIM(split.Item), 2) = 'QC'
   OR ISNUMERIC(split.Item) = 1
)

Demo with your sample data. But in general you should avoid multiple values in one column. Instead store it in separate rows in a linked table. That's called database normalization.

Upvotes: 1

Related Questions