Insert character into SQL string

sql insert-update
David Hall picture David Hall · Jun 25, 2013 · Viewed 44.7k times · Source

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 position from the right, such as: column value is 

0000050704

and I need it to be 

0000050-70-4

or value is 

0555256321

and it should be 

0555256-32-1

Can't see how this is done. Can anyone give me a little help?

Answer

Jaloopa picture Jaloopa · Jun 25, 2013

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 @txt varchar(100) = '0000050704'

--If using SQL Server, the STUFF() function is your friend
select REVERSE(STUFF(STUFF(REVERSE(@txt), 2, 0, '-'), 5, 0, '-'))

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

Related questions

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19); Let’s say …

Read More
How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?

Is there an easy way to INSERT a row when it does not exist, or to UPDATE if it exists, using one MySQL query?

Read More
Does DB2 have an "insert or update" statement?

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't …

Read More