CODEWITHSUNDEEP
sql-server
Darren Broderick DBro
Darren Broderick DBro

Reputation: 341

SQL - Column value manipulation -return dataset

I have column values = 1,2,3 AND 0,1 across 2 records.

When selecting both records from a view I am trying to remove the '1,' and '1'

My attempt is below;

CAST(CASE WHEN (column like '%1%') THEN (ReturnTheValueWithout 1 OR 1,) 
ELSE column END AS VARCHAR) AS NewColumnName

Upvotes: 1

Views: 72

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Solution1:

DECLARE @s VARCHAR(20) = '1,11,2,1,3,4,1'
SELECT  REPLACE(CASE WHEN LEFT(@s, 2) = '1,'
                          AND RIGHT(@s, 2) = ',1'
                     THEN SUBSTRING(@s, 3, LEN(@s) - 4)
                     WHEN LEFT(@s, 2) = '1,' THEN RIGHT(@s, LEN(@s) - 2)
                     WHEN RIGHT(@s, 2) = ',1' THEN LEFT(@s, LEN(@s) - 2)
                     ELSE @s
                END, ',1,', ',')

Output:

11,2,3,4

Solution2:

SELECT SUBSTRING(REPLACE(',' + @s + ',', ',1,', ','), 2, LEN(REPLACE(',' + @s + ',', ',1,', ','))-2)

Shorter version:

SELECT SUBSTRING(s, 2, LEN(s) - 2) FROM (SELECT REPLACE(',' + @s + ',', ',1,', ',') s)t

Solution3:

Most short:

SELECT REPLACE(',' + REPLACE(@s, ',1,', ',,1,') + ',', ',1,', '')

Solution4:

Above solutions failed here and there on some complex string. The following works on 

DECLARE @s VARCHAR(120) = '1,31,11,2,1,3,4,1234,1,1,1,1,1,1,1,1,1,sfds,23,12,11,1'

SELECT  REPLACE(REPLACE(REPLACE(CASE WHEN LEFT(@s, 2) = '1,'
                          AND RIGHT(@s, 2) = ',1'
                     THEN SUBSTRING(@s, 3, LEN(@s) - 4)
                     WHEN LEFT(@s, 2) = '1,' THEN RIGHT(@s, LEN(@s) - 2)
                     WHEN RIGHT(@s, 2) = ',1' THEN LEFT(@s, LEN(@s) - 2)
                     ELSE @s
                END, ',1,', ',,1,,'), ',1,', ''), ',,', ',')

Output:

31,11,2,3,4,1234,sfds,23,12,11

Upvotes: 0

ughai
ughai

Reputation: 9890

One way to do this is using REPLACE with other string functions like LEFT and STUFF

Query

SELECT LEFT(STUFF(REPLACE(',' + Col1 + ',',',1,',','),1,1,''),LEN(STUFF(REPLACE(',' + Col1 + ',',',1,',','),1,1,''))-1)

Test Script

DECLARE @v varchar(50)  = '1,2,3'

--SET @v = '0,1'
SELECT LEFT(STUFF(REPLACE(',' + @v + ',',',1,',','),1,1,''),LEN(STUFF(REPLACE(',' + @v + ',',',1,',','),1,1,''))-1)

Output

0
2,3

Edit

A shorter version with REVERSE

SELECT REVERSE(STUFF(REVERSE(STUFF(REPLACE(',' + Col1  + ',',',1,',','),1,1,'')),1,1,''))
FROM...

SQL Fiddle

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

You can use REPLACE:

SELECT REPLACE(REPLACE(column,'1,','')),',1','')
FROM TableName

Result:

2,3
0

Sample result in SQL Fiddle.

Upvotes: 1

Related Questions