CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pratik
Pratik

Reputation: 1512

Trim Special Char from SQL String

I am using SQL Server 2008

I have sql string in column with ; separated values. How i can trim the below value

Current string: ;145615;1676288;178829;

Output: 145615;1676288;178829;

Please help with sql query to trim the first ; from string

Note : The first char may be or may not be ; but if it is ; then only it should trim.

Edit: What i had tried before, although it doesn't make sense after so many good responses.

DECLARE 
@VAL VARCHAR(1000)
BEGIN
SET @VAL =';13342762;1334273;'
IF(CHARINDEX(';',@VAL,1)=1)
    BEGIN
      SELECT SUBSTRING(@VAL,2,LEN(@VAL))
    END 
ELSE
    BEGIN
     SELECT @VAL
    END
END

Upvotes: 0

Views: 226

Answers (4)

Andriy M
Andriy M

Reputation: 77717

A further development on @Aaron Bertrand's answer:

SELECT
  STUFF(col, 1, PATINDEX(';%', col), '')
FROM ...

PATINDEX is similar to LIKE in that it uses a pattern search, but being a function it also returns the position of the first match. In this case, since we a looking for a ; specifically at the beginning of a string, the position returned is going to be either 1 (if found) or 0 (if not found). If it is 1, the STUFF function will delete 1 character at the beginning of the string, and if the position is 0, STUFF will delete 0 characters.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

SELECT CASE WHEN col LIKE ';%' 
  THEN STUFF(col,1,1,'') ELSE col END
  FROM dbo.table;

Upvotes: 11

Darren
Darren

Reputation: 184

Here's an example:

DECLARE @v varchar(10)
SET @v = ';1234'

SELECT
    CASE
        WHEN LEFT(@v,1) = ';' THEN RIGHT(@v, LEN(@v) - 1)
        ELSE @v
    END

Upvotes: 2

D Stanley
D Stanley

Reputation: 152624

Just check the first character, and if it matches, start from the second character:

SELECT CASE WHEN SUBSTRING(col,1,1) = ';'
       THEN SUBSTRING(col,2,LEN(col))
       ELSE col
       END AS col

Upvotes: 6

Related Questions