set data in string at specific location using SQL

Question

Using data from a SQL db, I need to fill a string with 60 pieces of data set at specific locations. For example, data 1 will be at myString location 1 to 4, data 2 will be at 5 to 10, etc.

mySQL SELECT INSERT('Originalstring', 4, 5, ' insert ')

would be perfect but I am using SQL. How can I do this in SQL?

Your question is a little confusing are you wanting to just combine several sets of strings into one long string? or are you wanting to insert your custom string into a pre-existing string at set intervals? — Chad Portman
– Chad Portman, Commented Apr 13, 2016 at 18:57
Yes checked into concat(). The issue is my data may not fill the whole location size. Example: if data 1 is supposed to be in position 1 to 4 but is only length of 2, data 2 must still start at position 5. — Sonia
– Sonia, Commented Apr 13, 2016 at 19:00
Sorry. combine data retrieved from db (int, strings, datetime, etc) into one long string at set intervals. I will have to convert int into string but yes in the end, combined into one long string at set intervals. — Sonia
– Sonia, Commented Apr 13, 2016 at 19:04
See my answer for (what seems to be, anyway) the tsql equivalent of mysql's SELECT INSERT. Although, if possible, I'd recommend doing this in the code instead of in the database. String manipulation is clunky and weird in sql and you'd be much better served having the application logic take care of this and pass the updated value down. — levelonehuman
– levelonehuman, Commented Apr 13, 2016 at 19:06

levelonehuman · Accepted Answer · 2016-04-13 18:57:31Z

1

You want to use STUFF.

declare @originalString varchar(50) = 'This is my string',
        @stringToStuff varchar(10) = 'new '

SELECT  STUFF(@originalString, 12, 0, @stringToStuff)

This will insert the string @stringToStuff at index 12 of @originalString (immediately following the space after my).

answered Apr 13, 2016 at 18:57

levelonehuman

1,50515 silver badges24 bronze badges

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

1 Comment

Chad Portman Over a year ago

This is awesome I did not know Stuff was a thing in SQL. This could be useful.

Collectives™ on Stack Overflow

set data in string at specific location using SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related