Insert String into another string
-
create table test (anystring varchar(8000))
insert test
select 'abcdefghijkl'
go
How do i then insert 1234 in between the e and the f.
Do i have to split the string into two and tag the 1234 on the end of the first and then rejoin or is there a way which i cen simply insert it. I have tried using stuff but it over writes what is there.
Thanks,
Jules
-
DECLARE @Insert int,
@EndString int
SELECT @Insert = CHARINDEX('e',Anystring) FROM Test
SELECT @EndString = (LEN(Anystring) - @Insert) FROM Test
UPDATE Test
SET Anystring = SUBSTRING(Anystring,1,@Insert) + '1234' + SUBSTRING(Anystring,(@Insert + 1),@EndString)
FROM Test
SELECT Anystring
FROM Test
-
yep thanks had that way of doing it. But wondered if there was a way you could insert rather than spilt and tag. Wanted to add a parameter on every stored proceedure used for searches. In this case @languageid. If your interested it works nicely.
declare @tab table (id int identity, definition nvarchar(max))
declare @i int, @no int, @sql nvarchar(max)
insert @tab
select distinct 'ALTER ' +substring(definition,charindex('create',definition)+ 7 , len(definition))
from sys.all_objects sp
left join sys.sql_modules smsp
ON smsp.object_id = sp.object_id
where substring(sp.name, 5,6) = 'search'
and (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(SCHEMA_NAME(sp.schema_id)=N'dbo')
and sp.name not in (select o.name
from sysobjects o
join sys.all_parameters p
on p.object_id = o.id
where o.type = 'p'
and substring(o.name, 5,6) = 'search'
and p.name = '@languageid'
)
select @no = scope_identity(), @i = 1
while @i <= @no
begin
select @sql = substring(definition, 1,charindex('@', definition)-1)+ '@LanguageID int = null,'+char(10)
+substring(definition, charindex('@', definition),len(definition))
from @tab
where id = @i
print @sql
exec (@sql)
set @i =@i +1
end
-
No No No. Check out STUFF in the BOL.
SELECT STUFF('abcdefghijkl', 6, 0, '1234')
This says: STUFF the expression 1234 into abcdefghijkl starting at the 6th position and delete 0 characters.
-SQLBill
-
SQLBill,
I'm amazed at how many folks don't know about the STUFF function... you beat me to it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good stuff
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply