Solved

sql string insert at position

Posted on 2012-03-26
4
312 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now