Split a sentence into substrings.

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 2005 Forums

Transact-SQL (2005)

Split a sentence into substrings.

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-26 : 19:00:43

Hi,
There is a record which likes:
"There is a record which likes this."

I want to get an array
"There"
"is"
"a"
"record"
"which"
"likes"
"this"

What kinds of query it should be?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-26 : 19:08:05

Considering that SQL Server doesn't support arrays, you may have some difficulty. Do you want to return a set of rows? It helps to be specific -- when you say "there is a record", that's not very clear.

- Jeff
http://weblogs.sqlteam.com/JeffS

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-08-26 : 19:52:13

It is a column. Sorry for unclear.
Return a set of rows is fine.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 21:22:36

[code]SELECT *
FROM dbo.fnParseList(' ', 'There IS a record which likes this')

OR

SELECT *
FROM yourtable t
CROSS apply dbo.fnParseList(' ', t.yourcolumn)
[/code]
get fnParseList from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-08-27 : 05:35:38

Hi,

try with this

DEclare @Remarks_Id_Str Varchar(100)

Set @Remarks_Id_Str = 'There is a record which likes this.'

SELECT '"' + (SUBSTRING(OrderID, number+1, CHARINDEX(' ', OrderID, number+1)-number-1))+ '"' AS OrderID
FROM
(SELECT '' + @Remarks_Id_Str + ' ' AS OrderID) AS InnerQuery
JOIN (Select Number From Master..spt_Values where Type = 'P') n
ON n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ' '

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-03 : 11:09:20

[code]
declare @Sentence varchar(500)
set @Sentence = 'This is a sample sentence.'

while charindex(' ', @Sentence) > 0 set @Sentence = replace(@Sentence, ' ', ' ')
;
with Fragments as
(select right(@Sentence, len(@Sentence) - number) as Fragment
from master..spt_values
where type = 'P'
and number <= len(@Sentence)
and substring(@Sentence, number, 1) = ' ')
select left(Fragment, charindex(' ', Fragment + ' ')-1) as Word
from Fragments
[/code]

Boycotted Beijing Olympics 2008

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 07:58:47

I am using another way but get an error when login with sa.
It works in windows login. Why?

declare @Remarks_Id_Str Varchar(255)
      Set @Remarks_Id_Str = 'Test splitting a sentence.'
      declare @xml xml
      set @xml = cast('<tag>' + replace(@Remarks_Id_Str, ' ','</tag><tag>') + '</tag>' as xml)
      select T.i.value('.', 'varchar(255)') from @xml.nodes('tag') T(i)

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '.'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 08:06:08

quote:

Originally posted by zhshqzyc

I am using another way but get an error when login with sa.
It works in windows login. Why?

declare @Remarks_Id_Str Varchar(255)
      Set @Remarks_Id_Str = 'Test splitting a sentence.'
      declare @xml xml
      set @xml = cast('<tag>' + replace(@Remarks_Id_Str, ' ','</tag><tag>') + '</tag>' as xml)
      select T.i.value('.', 'varchar(255)') from @xml.nodes('/tag') T(i)

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '.'.

put a / and try

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 08:53:38

Thanks.

Let me try.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 09:17:00

Still wrong.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-09-04 : 09:57:10

declare @x varchar(50)
declare @x1 xml
set @x='There is a record which likes this'
set @x1= '' + replace(@x,' ' ,'') + ''
select x.i.value('.','varchar(50)')as Arr from @x1.nodes('i')x(i)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 09:58:16

quote:
Originally posted by zhshqzyc

Still wrong.

Is this your full query? if not please post remaining part. cant spot anything obvious in posted code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 09:59:44

quote:
Originally posted by ayamas

declare @x varchar(50)
declare @x1 xml
set @x='There is a record which likes this'
set @x1= '' + replace(@x,' ' ,'') + ''
select x.i.value('.','varchar(50)')as Arr from @x1.nodes('/i')x(i)

still you havent included /

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 10:02:44

Even I run this

declare @xml xml

Then


Msg 2715, Level 16, State 3, Line 1
Column or parameter #-1: Cannot find data type xml.
Parameter '@xml' has an invalid data type.

Why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:09:41

quote:

Originally posted by zhshqzyc

Even I run this

declare @xml xml

Then


Msg 2715, Level 16, State 3, Line 1
Column or parameter #-1: Cannot find data type xml.
Parameter '@xml' has an invalid data type.

Why?

this means you're not using sql 2005 or your compatibilty level is 80 or below. xml datatype is available only in sql 2005 with compatibility 90.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 10:16:06

Is there any way to reslove this issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:17:37

quote:
Originally posted by zhshqzyc

Is there any way to reslove this issue?

are you using sql 2005?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 10:20:45

I am using sql 2005 management studio express.
However I have to connect an sql server 2000 instance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:29:51

quote:
Originally posted by zhshqzyc

I am using sql 2005 management studio express.
However I have to connect an sql server 2000 instance.

then only way you can resolve it is using varchar() instead of xml. and use OPENXML to retrive data

declare @x varchar(50),@i int
set @x='There is a record which likes this'
set @x= '<root><i>' + replace(@x,' ' ,'</i><i>') + '</i></root>'
EXEC sp_xml_preparedocument @i OUTPUT, @x
SELECT * FROM     OPENXML(@i, '/root') 
        WITH (i     VARCHAR(50))
EXEC sp_xml_removedocument @i

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-09-04 : 10:36:54

Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 4
XML parsing error: Element was not closed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:42:29

quote:
Originally posted by zhshqzyc

Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 4
XML parsing error: Element was not closed.

increase length of varchar and see

declare @x varchar(1000),@i int
set @x='There is a record which likes this'
set @x= '<root><i>' + replace(@x,' ' ,'</i><i>') + '</i></root>'
EXEC sp_xml_preparedocument @i OUTPUT, @x
SELECT * FROM     OPENXML(@i, '/root') 
        WITH (i     VARCHAR(50))
EXEC sp_xml_removedocument @i

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources