How to remove the first character if it is a speci

Question

I currently have a table Telephone it has entries like the following:

9073456789101
[phone]
0773456789101

What I want to do is remove only the 9 from the start of all the entries that have a 9 there but leave the others as they are.

any help would be greatly appreciated.

Answer 1

UPDATE dbo.Telephone 
  SET column_name = SUBSTRING(column_name, 2, 255)
  WHERE column_name LIKE '9%';

Answer 2

Update Telephone set number = RIGHT(number,LEN(number)-1) WHERE number LIKE '9%';

Answer 3

I recently solved a similar problem with a combination of RIGHT(), LEN() & PATINDEX(). PATINDEX will return the integer 1 when it finds a 9 as the first character and 0 otherwise. This method allows all records to be returned at once without a CASE WHEN statement.

SELECT
    RIGHT(number, LEN(number) - PATINDEX('9%', number))
FROM Telephone

Answer 4

While all other answer are probably also working, I'd suggest to try and use STUFF function to easily replace a part of the string.

UPDATE Telephone
SET number = STUFF(number,1,1,'')
WHERE number LIKE '9%'

SQLFiddle DEMO

Answer 5

DECLARE @STR nvarchar(200) = 'TEST'
SET @STR = STUFF(@STR,1,1,'')
PRINT @STR

Result will be "EST"

Answer 6

Here is the code and a SQLFiddle

SELECT CASE 
WHEN substring(telephone_number, 1, 1) <> '9'
  THEN telephone_number
ELSE substring(telephone_number, 2, LEN(telephone_number))
END
FROM Telephone