update with substring - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

update with substring

Author

Topic

shpinoza1980
Starting Member

17 Posts

Posted - 2011-03-28 : 09:41:45

Hello,

my table looks like this:

[description] nvarchar (500)
[source] nvarchar (100)

in the [description] column I have long strings that looks like this:

some text some text SOURCE text to retrieve SOURCE some text some text

I want to update the [source] column with the substring that capture ONLY the text between the two "source" words, which will result in:

text to retrieve

what is the correct syntax for that?

thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-28 : 09:48:28

I leave it to you to apply this to a table...


Declare @str varchar(1000),
	@Source varchar(100)

Set @str = 'some text some text SOURCE text to retrieve SOURCE some text some text'
Set @Source = 'SOURCE'

Select
	@str,
	substring(@str,
		charindex(@Source,@str,1)+LEN(@Source),
		charindex(@Source,@str,charindex(@Source,@str,1)+1) - (charindex(@Source,@str,1)+LEN(@Source))
	)

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

shpinoza1980
Starting Member

17 Posts

Posted - 2011-03-28 : 12:29:52

thanks, but this part I already got down...

my problem is actually how to apply this to a table

thanks a lot

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-28 : 12:38:44

A normal update didn't work for you? What did you try?

Update YourTable
Set
Source = substring(...blah blah...)
From YourTable

Just so you know... I think this is a bad idea if these are the only 2 columns... where is your key? or what happens now that [Source] has been overwritten?

anyway... food for thought.

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources