How to remove the first character if it is a speci

问题:

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.

回答1:

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

回答2:

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

回答3:

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

回答4:

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

回答5:

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

回答6:

Stuff is a great function for this. However, using it with an update statement with a where clause is great, but what if I was doing an insert, and I needed all of the rows inserted in one pass. The below will remove the first character if it is a period, does not use the slower case statement, and converts nulls to an empty string.

DECLARE @Attachment varchar(6) = '.GIF',
        @Attachment2 varchar(6)

SELECT
  @Attachment2 = ISNULL(ISNULL(NULLIF(LEFT(@Attachment, 1), '.'), '') + STUFF(@Attachment, 1, 1, ''), '')

SELECT
  @Attachment2

回答7:

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

Result will be "EST"