CODEWITHSUNDEEP
sqlsql-servert-sqlssms
tankard
tankard

Reputation: 13

SQL Server - How can I add 1 or more specific characters in the middle of a string?

Good night, folks. The question is pretty clear: I need to add 2 specific characters in the middle of a string.

Example:

(the way it is now)

ID

12345678    
23456789    
34567891    
45678912

(the way I need to be)

ID

12-34567-8    
23-45678-9    
34-56789-1    
45-67891-2

I've tried with STUFF and CONCAT but no dice.

Upvotes: 0

Views: 86

Answers (2)

tankard
tankard

Reputation: 13

@John Cappeletti Thanks for your answer! I can't explain why, but it seems that my SSMS (SQL Server 2008 R2) didn't like 2 STUFFs to be executed in sequence. Just divided them into 2 sentences and voila...solved!

Sorry for any inconvenience caused while my question.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81970

You can use STUFF or even Format() if 2012+

The following assume ID is not an INT.

Example

Declare @YourTable Table ([ID] varchar(50))
Insert Into @YourTable Values 
 (12345678)
,(23456789)
,(34567891)
,(45678912)

Select * 
      ,ViaFormat = format(cast(ID as int),'00-00000-0')
      ,ViaStuff  = stuff(stuff(ID,8,0,'-'),3,0,'-')
 From @YourTable

Returns

ID          ViaFormat   ViaStuff
12345678    12-34567-8  12-34567-8
23456789    23-45678-9  23-45678-9
34567891    34-56789-1  34-56789-1
45678912    45-67891-2  45-67891-2

Upvotes: 4

Related Questions