Solved

sql string insert at position

Posted on 2012-03-26
4
335 Views
Last Modified: 2012-04-12
Is there any SQL funtion that allows me to insert a string at a specific position within a string?  As in if I have the string "Hello" Can I insert the text "y je"text specifically at position 3 to achieve the result "Hey jello"
0
Comment
Question by:zintech
  • 2
4 Comments
 
LVL 2

Expert Comment

by:dantheanswerman
ID: 37768178
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37768305
Although STUFF is very useful and well worth reading about, it REPLACES characters instead of just inserting in the desired place.

Here are a couple of ways to do it, CHARINDEX is more flexible here and you can change it as you to insert after whatever character/sequence you want.:

declare @string varchar(200)
set @string = 'hello'

select left(@string,2)+'y je'+ right(@string,3)

--OR

select substring(@string,0, len(@string)-charindex( 'e',@string)) + 'y je' + substring(@string, charindex( 'e',@string)+1,len(@string))

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37769531
>>it REPLACES characters instead of just inserting in the desired place.<<
Actually that is not true.  It can insert.  You may want to re-read the documentation or see for yourself:
DECLARE @Value varchar(20) = 'Hello'
SELECT STUFF(@Value, 3, 0, 'y je')

Open in new window

Output:
Hey jello
0
 
LVL 9

Expert Comment

by:OCDan
ID: 37769948
My bad, thanks acperkins
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question