How to pull the words from records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to pull the words from records?

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-07-06 : 15:50:06

Hello,

I have to write a script for search using like.
In the following first record user wants to search by keword "working", the result will be display on just first sentence from the first record, we don't need to display all the sentences also if the key word "working" has on multiple sentence it suppose to be display second sentence.

So anybody have idea?

Declare @tbl1 table
(ID INT,
txtBody varchar(max)
)

INSERT INTO @tbl1
SELECT 1,'This forum is specifically for people that are working with SQL Server Programming for the first time. Anything T-SQL or SQL Server client related is welcome.'

INSERT INTO @tbl1
SELECT 2,'This forum is for new SQL Server administrators. Anything related to backups, indexing, maintenance, sizing, etc.'

INSERT INTO @tbl1
SELECT 3,'This section is for any originally written scripts you d like to share with the group. Please don t repost content from books or other web sites unless you are the original author or copyright holder.'

select * from @tbl1 where txtBody like '%working%'

The output should be like :

"'This forum is specifically for people that are working with SQL Server Programming for the first time."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 15:56:36

SELECT Data
FROM @tbl1
CROSS APPLY dbo.fnParseList('.', txtBody) AS f
WHERE Data LIKE '%working%'

N 56°04'39.26"
E 12°55'05.63"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-07-06 : 16:09:52

What is fnParseList? Is this system function or??
I am using ms sql server 2005, i do not see this on system.
So getting err: Invalid object name 'dbo.fnParseList'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 16:23:11

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

N 56°04'39.26"
E 12°55'05.63"

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 16:24:33

except for the fact that it will scan the table, doesn't your solution work for you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 16:26:39

When I inspected the sample data, I saw that there was two sentences in the first INSERT.
And the expected output only fetched the sentence with the search word included.

N 56°04'39.26"
E 12°55'05.63"

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-07-06 : 16:29:01

Thanks.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-07-06 : 16:41:36

Thanks Peso,

Really i am looking for this:

CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@Text)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
END

INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
END

RETURN
END

Declare @tbl1 table
(ID INT,
txtBody varchar(max)
)

INSERT INTO @tbl1
SELECT 1,'This forum is specifically for people that are working with SQL Server Programming for the first time. Anything T-SQL or SQL Server client related is welcome.'

INSERT INTO @tbl1
SELECT 2,'This forum is for new SQL Server administrators. Anything related to backups, indexing, maintenance, sizing, etc.'

INSERT INTO @tbl1
SELECT 3,'This section is for any original written scripts you d like to share with the group. Please don t repost content from books or other web sites unless you are the original author or copyright holder.'

SELECT Data
FROM @tbl1
CROSS APPLY dbo.fnParseList('.', txtBody) AS f
WHERE Data LIKE '%working%'

SELECT Data
FROM @tbl1
CROSS APPLY dbo.fnParseList('.', txtBody) AS f
WHERE Data LIKE '%original%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 01:58:22

or

select left(txtBody,charindex('.',txtBody)) from @tbl1 where txtBody like '%working%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 03:00:06

What if the wanted text is in the second sentence?

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 05:09:29

quote:
Originally posted by Peso

What if the wanted text is in the second sentence?

N 56°04'39.26"
E 12°55'05.63"

Good Point. Spliting based on . is the way

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources