CODEWITHSUNDEEP
sql-servert-sqlsql-server-2000
Big Pimpin
Big Pimpin

Reputation: 437

Format SQL Server Field

I need a way to remove a comma if that is the 1st character in a field. For example, the data would be ,Monday instead of reading just Monday How can I 1st check if the comma is the 1st character in a field, and if it is remove it?

Upvotes: 0

Views: 42

Answers (3)

StackUser
StackUser

Reputation: 5398

Try this,

DECLARE @string VARCHAR(50)=',Monday'

SELECT replace(LEFT(@string, 1), ',', '')
       + substring(@string, 2, len(@string))

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it with STUFF:

DECLARE @str VARCHAR(20) = ',Monday'

SELECT STUFF(LTRIM(@str), 1, CASE WHEN CHARINDEX(',', LTRIM(@str)) = 1 THEN 1 ELSE 0  END, '')

If there is a ',' at the beginning of the string (after left trimming is performed) then replace it with '', otherwise set length parameter of STUFF to 0, i.e. do nothing.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452957

SELECT CASE
         WHEN YourCol LIKE ',%' THEN SUBSTRING(YourCol, 2, 8000)
         ELSE YourCol
       END

Upvotes: 5

Related Questions