CODEWITHSUNDEEP
sqlsql-server
JVGBI
JVGBI

Reputation: 575

SQL UPDATE specific characters in string

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.

Upvotes: 2

Views: 11913

Answers (5)

Paweł Tajs
Paweł Tajs

Reputation: 482

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

Upvotes: 2

user7715598
user7715598

Reputation: 

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

Upvotes: 0

Khyati Shah
Khyati Shah

Reputation: 31

Use this,

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

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

Upvotes: 3

nazark
nazark

Reputation: 1240

try,

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

Upvotes: 0

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

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

Upvotes: 0

Related Questions