Query question - 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 2000 Forums

Transact-SQL (2000)

Query question

Author

Topic

rfedeleo
Starting Member

2 Posts

Posted - 2003-07-24 : 11:06:12

How would you extract data from a field within Query analyzer. For example if I had a field which stored an entire sentence like "Hello my name is Rog." How would I create a procedure or function to extract anything after the word "is" Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-24 : 11:38:03

something like
select right(fld,charindex(' si ',reverse(fld))-1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 11:40:34

Only because I just wrote it for someone else...



	DECLARE @IP varchar(255), @x Int, @y Int, @str varchar(800)
	SELECT @str = 'Hello my name is Roger Rabbit'
	SET @x = CHARINDEX('is',@str, 1)
	SET @y = LEN(@str) - CHARINDEX('is', @str,1) + 1
	SET @IP = SUBSTRING(@str, @x, @y)
	SELECT @IP, @x, @y

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 11:47:01

Nigel,

How cool is that...How much more don't I know (hold on, I'll get a dumpster)


	DECLARE @IP varchar(255), @x Int, @y Int, @str varchar(800)
	SELECT @str = 'Hello my name is Roger Rabbit'
	SET @x = CHARINDEX('is',@str, 1)
	SET @y = LEN(@str) - CHARINDEX('is', @str,1) + 1
	SET @IP = SUBSTRING(@str, @x, @y)
	SELECT @IP, @x, @y

	select right(@str,charindex(' si ',reverse(@str))-1)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources