How to add substring in SQL

Question

How to add substring in SQL ?

DECLARE @ssn varchar(11)
SELECT @ssn = '123456789'

Actually, i have the value in my database as 123-45-6789

I want to do it one query to get the output as 12-456789

Expected output is 12-3456789

Is it on purpose, that the "3" is missing in your line "I want to do it in one query..."? — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 8, 2015 at 19:41

Gordon Linoff · Accepted Answer · 2015-12-08 19:42:49Z

2

If you want to add a hyphen, you can use stuff():

select @ssn = stuff(@ssn, 3, 0, '-')

EDIT:

Removing a hyphen is quite similar:

select @ssn = stuff(@ssn, 7, 1, '')

answered Dec 8, 2015 at 19:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

it works .. but i did some change in my actual post. could you please help me with that

Gottfried Lesigang · Accepted Answer · 2015-12-08 19:40:45Z

1

Try it with STUFF:

EDIT: Your follow-up question:

DECLARE @ssn varchar(100);
SELECT @ssn = '123-45-6789';

SELECT STUFF(REPLACE(@ssn,'-',''),3,0,'-');

The old question:

DECLARE @ssn varchar(11);
SELECT @ssn = '123456789';

SELECT STUFF(@ssn,3,0,'-');

answered Dec 8, 2015 at 19:38

67.6k9 gold badges60 silver badges124 bronze badges