Insert Character Into Sql String

I have an nvarchar column I need to insert a hyphen at fixed points within the string. The hyphen need to go between the rightmost character and the next, and again in the 3rd posi

Solution 1:

Assuming the strings can be a variable length, you'll need to use REVERSE() or lots of nasty looking LEN() values in your expression.

declare@txtvarchar(100) ='0000050704'--If using SQL Server, the STUFF() function is your friendselect REVERSE(STUFF(STUFF(REVERSE(@txt), 2, 0, '-'), 5, 0, '-'))

--if not you'll need to concatenate SUBSTRING()sselect REVERSE(SUBSTRING(REVERSE(@txt), 1, 1) +'-'+SUBSTRING(REVERSE(@txt),2, 2) +'-'+SUBSTRING(REVERSE(@txt),4, LEN(@txt)))

Solution 2:

You can use this easy function:

CREATEFUNCTION[dbo].[SetHyphen] (@S varchar(50)) RETURNSvarchar(52)
BEGINRETURNSTUFF(STUFF(@S,LEN(@S)-2,0,'-'),LEN(@S)+1,0,'-')
END

For example:

select[dbo].[SetHyphen]('0000050704')
0000050-70-4

Solution 3:

It depends on your SQL server. Please check the documentation on how to manipulate strings - I will suppose SUBSTRING function.

In MS SQL Server you can do sth. like this:

UPDATE YourTableName SET 
    YourFieldName =SUBSTRING(YourFieldName, 1,7) + "-" +SUBSTRING(YourFieldName, 7,2) + "-" +SUBSTRING(YourFieldName, 9,1)

This will split your field content in three parts and rebuild it with separators...

Before running the query I will suggest you try it as plain SELECT to see if it works as needed:

SELECTSUBSTRING(YourFieldName, 1,7) + "-" +SUBSTRING(YourFieldName, 7,2) + "-" +SUBSTRING(YourFieldName, 9,1)
FROM YourTableName

Take care that the query is without WHERE condition and thus will affect ALL rows of your table.