CODEWITHSUNDEEP
sqlsql-servercharacter
Scorpion99
Scorpion99

Reputation: 248

updating a text with additional characters in SQL

I have the following value format :

1234567890

I want to convert it to the following format : 123-45A67890

Table Name: Test

Column Name : MyCode

Note that I am using Microsoft SQL 2012.

Upvotes: 0

Views: 1091

Answers (3)

Vikrant
Vikrant

Reputation: 5036

Here what tried with a variable @a: 

declare @a varchar(100)='1234567890'

select STUFF(STUFF(@a,4,0,'-'),7,0,'A') --gives--> '123-45A67890'

Likely you can use it to update your table, which

  1. Adds Hyphen (-) after first 3rd character,
  2. Adds letter 'A' after 2 letters just after hyphen... 
update Test set MyCode = STUFF(STUFF(MyCode,7,0,'A'),4,0,'-')

More about STUFF() function in SQL

Upvotes: 0

jarlh
jarlh

Reputation: 44696

Does SQL Server support the PASTE function?

paste(paste('1234567890',7,0,'A'),4,0,'-')

For example:

select paste(paste(column_name,7,0,'A'),4,0,'-') from table_name

or

update table_name set column_name = paste(paste(column_name,7,0,'A'),4,0,'-')

Upvotes: 0

ughai
ughai

Reputation: 9880

You can use STUFF . Something like this

DECLARE @v VARCHAR(15) = '1234567890'

SELECT STUFF(STUFF(@v,4,0,'-'),7,0,'A')

Your SELECT would be 

SELECT STUFF(STUFF(MyCode,4,0,'-'),7,0,'A')
 FROM Test

Your UPDATE would be 

UPDATE Test
SET MyCode = STUFF(STUFF(MyCode,4,0,'-'),7,0,'A')

Upvotes: 1

Related Questions