SQL UPDATE specific characters in string

Question

I have a column with the following values (there is alot more):

20150223-001
20150224-002
20150225-003

I need to write an UPDATE statement which will change the first 2 characters after the dash to 'AB'. Result has to be the following:

 20150223-AB1
 20150224-AB2
 20150225-AB3

Could anyone assist me with this?

Thanks in advance.

Will it always be 8 characters before the dash? Then you can use substring and concat. — jarlh, May 10 '17 at 07:51
See if it can help you. http://stackoverflow.com/questions/9260044/how-to-split-a-string-after-specific-character-in-sql-server-and-update-this-val — Ric_R, May 10 '17 at 07:53
yes its always 8 charachters before the dash but the characters after the dash can vary. It has to update the first two after the dash. — JVGBI, May 10 '17 at 07:53
If the input is `20150223-00001`, what is the expected Output ? `20150223-AB001`, or `20150223-ABCD1`, ?? — ahmed abdelqader, May 10 '17 at 08:02
What about values like `20170123-015` or `20170123-123`, do those also occur? And how should those be handled? — BdR, May 11 '17 at 07:36

score 3 · Answer 1 · answered May 10 '17 at 08:53

3

Use this,

DECLARE @MyString VARCHAR(30) = '20150223-0000000001'

SELECT STUFF(@MyString,CHARINDEX('-',@MyString)+1,2,'AB')

answered May 10 '17 at 08:53

Khyati Shah

31
3

score 2 · Answer 2 · answered May 10 '17 at 10:57

If there is a lot of data, you could consider to use .WRITE clause. But it is limited to VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types.

If you have one of the following column types, the .WRITE clause is easiest for this purpose, example below:

UPDATE Codes
SET val.WRITE('AB',9,2)
GO

Other possible choice could be simple REPLACE:

UPDATE Codes
SET val=REPLACE(val,SUBSTRING(val,10,2),'AB')
GO

or STUFF:

UPDATE Codes
SET val=STUFF(val,10,2,'AB')
GO

I based on the information that there is always 8 characters of date and one dash after in the column. I prepered a table and checked some solutions which were mentioned here.

CREATE TABLE Codes(val NVARCHAR(MAX))

INSERT INTO Codes
SELECT TOP 500000 CONVERT(NVARCHAR(128),GETDATE()-CHECKSUM(NEWID())%1000,112)+'-00'+CAST(ABS(CAST(CHECKSUM(NEWID())%10000 AS INT)) AS NVARCHAR(128))
FROM sys.columns s1 CROSS JOIN sys.columns s2

I run some tests, and based on 10kk rows with NVARCHAR(MAX) column, I got following results:

+---------+------------+
| Method  |    Time    |
+---------+------------+
| .WRITE  | 28 seconds |
| REPLACE | 30 seconds |
| STUFF   | 15 seconds |
+---------+------------+

As we can see STUFF looks like the best option for updating part of string. .WRITE should be consider when you insert or append new data into string, then you could take advantage of minimall logging if the database recovery model is set to bulk-logged or simple. According to MSDN articleabout UPDATE statement: Updating Large Value Data Types

nazark · Answer 3 · 2017-05-10T08:28:06.283

0

try,

update table set column=stuff(column,charindex('-',column)+1,2,'AB')

edited May 10 '17 at 08:28

answered May 10 '17 at 08:01

nazark

1,240
2
10
15

it is not working if the input is `20150223-0000000001`, plz check my answer ? – ahmed abdelqader May 10 '17 at 08:09

score 0 · Answer 4 · 2017-05-10T08:57:32.813

Declare @Table1 TABLE (DateValue Varchar(50))
INSERT INTO @Table1
SELECT '20150223-000000001' Union all
SELECT '20150224-000000002' Union all
SELECT '20150225-000000003'

SELECT DateValue,
CONCAT(SUBSTRING(DateValue,0,CHARINDEX('-',DateValue)),
REPLACE(LEFT(SUBSTRING(DateValue,CHARINDEX('-',DateValue)+1,Len(DateValue)),2),'00','-AB'),
SUBSTRING(DateValue,CHARINDEX('-',DateValue)+1,Len(DateValue))) AS ExpectedDateValue
FROM @Table1

OutPut

   DateValue                ExpectedDateValue
---------------------------------------------
   20150223-000000001   20150223-AB000000001
   20150224-000000002   20150224-AB000000002
   20150225-000000003   20150225-AB000000003

To Update

Update @Table1
SEt DateValue= CONCAT(SUBSTRING(DateValue,0,CHARINDEX('-',DateValue)),
REPLACE(LEFT(SUBSTRING(DateValue,CHARINDEX('-',DateValue)+1,Len(DateValue)),2),'00','-AB'),
SUBSTRING(DateValue,CHARINDEX('-',DateValue)+1,Len(DateValue)))
From @Table1

SELECT * from @Table1

OutPut

    DateValue
   -------------
20150223-AB000000001
20150224-AB000000002
20150225-AB000000003

It is not working if the input is `20150223-0000000001`, plz check my answer ? — ahmed abdelqader, May 10 '17 at 08:10
Thanx i just chnage my code it works for above data also preceeding with more zero's — , May 10 '17 at 09:00

score 0 · Answer 5 · answered May 10 '17 at 08:07

0

According to the OP Comment:-

Its always 8 charachters before the dash but the characters after the dash can vary. It has to update the first two after the dash.

use the next simple code:-

DECLARE @MyString VARCHAR(30) = '20150223-0000000001'

SELECT REPLACE(@MyString,SUBSTRING(@MyString,9,3),'-AB')

Result:-

20150223-AB00000001

answered May 10 '17 at 08:07

ahmed abdelqader

3,409
17
36

Risky if the same sequence of characters can also appear in other positions *as well*. This would replace all instances rather than just those appearing at positions 9-11. – Damien_The_Unbeliever May 10 '17 at 08:53
You are right, in the end the code is created based on OP requirements. – ahmed abdelqader May 10 '17 at 08:57

SQL UPDATE specific characters in string

5 Answers5