CODEWITHSUNDEEP
sqlsql-server-2008
w3n2u
w3n2u

Reputation: 333

How to remove the 1st character from a column in SQL Server

I have a Table, Table A, and in table A I have Field A. There are values in field A like the following:

Street A
Street B
,Street C
Street D
etc

I would like to know if there is any SQL that will allow me to either remove the 1st character from Field A where there is a ,.

I have know idea where to start I can select all the rows which have a , in Field A but I don't know where to start when trying to remove it.

Upvotes: 6

Views: 43545

Answers (4)

Martin Smith
Martin Smith

Reputation: 452957

UPDATE YourTable
SET    YourCol = SUBSTRING(YourCol, 2, 0+0x7fffffff) 
WHERE YourCol LIKE ',%'

Upvotes: 3

anon
anon

Reputation:

You could use the RIGHT, LEN and RTRIM functions

UPDATE TableA
SET FieldA = RIGHT(RTRIM(FieldA), LEN(FieldA) - 1)
WHERE FieldA LIKE ',%'

Example

Upvotes: 4

Serge
Serge

Reputation: 6692

If you'd rather not care about the length, STUFF is the right candidate :

UPDATE YourTable
SET    YourCol = STUFF(YourCol, 1, 1, '')
WHERE YourCol LIKE ',%'

Upvotes: 13

Jakub Konecki
Jakub Konecki

Reputation: 46008

You can use TSQL SUBSTRING function

http://msdn.microsoft.com/en-us/library/ms187748.aspx

Use LEN to get the length of the field.

http://msdn.microsoft.com/en-us/library/ms190329.aspx

SUBSTRING(FieldA, 2, LEN(FieldA) - 1)

Upvotes: 2

Related Questions