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

Question

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.

score 13 · Answer 1 · answered Jun 20 '13 at 13:15

13

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 ',%'

answered Jun 20 '13 at 13:15

Serge

To be clear there is no particular need to care about the length with `SUBSTRING` either - it doesn' t matter if it is over specified so you can set it to max int. – Martin Smith Nov 12 '17 at 11:49

score 4 · Answer 2 · 2013-06-20T14:16:01.477

4

You could use the RIGHT, LEN and RTRIM functions

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

edited Jun 20 '13 at 14:16

answered Jun 20 '13 at 12:27

@MartinSmith thanks for pointing that out, I've updated it to use the the datalength function – Jun 20 '13 at 13:05
Of course if their column datatype is `UNICODE` or they are on a double byte collation this may not be correct though. – Martin Smith Jun 20 '13 at 13:11
i updated for unicode, not sure if double byte collation makes any difference to data length though – Jun 20 '13 at 13:14
[Example of that here](http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-sql-server/8250586#8250586) – Martin Smith Jun 20 '13 at 13:15
If you are determined to use `RIGHT` probably the easiest way would be `RIGHT(RTRIM(FieldA), LEN(FieldA) - 1)` – Martin Smith Jun 20 '13 at 13:32
That seems so over the top, is there any benefit to using the `RIGHT` function instead of the `SUBSTRING` function – Jun 20 '13 at 14:18

Martin Smith · Accepted Answer · 2017-11-12T11:50:08.247

3

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

edited Nov 12 '17 at 11:50

answered Jun 20 '13 at 11:55

Martin Smith

score 2 · Answer 4 · answered Jun 20 '13 at 11:54

2

You can use TSQL SUBSTRING function

Use LEN to get the length of the field.

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

answered Jun 20 '13 at 11:54

Jakub Konecki

4 Answers4