Splitting a sentence into words ? 1

[Recce]

Hi everyone,

I need to split a sentence into it's seperate words and those words can then be in their own columns.

I have managed to split the first word by doing the following:

SELECT
BRANCHNAME,
SUBSTRING(BRANCHNAME, 1, NULLIF(CHARINDEX(' ', BRANCHNAME) - 1, -1)) AS [FirstWord],
SUBSTRING(BRANCHNAME, CHARINDEX(' ', BRANCHNAME) + 1, LEN(BRANCHNAME)) AS [Rest]
from LOTUS_REP.dbo.GARNADDRESS
where BRANCHNAME = 'Bright Home & Hardware'

I now have a problem in order to split the last three 'Home','&','Hardware'

Could anyone maybe suggest a solution of how I can split the remaining 3 into their own columns ?

"We know nothing but, that what we know is not the truth..." - Me

 

[gmmastros]

A numbers table usually takes just a couple megabytes and will make this query faster and easier to accomplish.

Do you have a numbers table in your database? If not, would you be willing to add one?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Recce]

Yes,that should be fine. I'm willing to add one or two...

"We know nothing but, that what we know is not the truth..." - Me

 

[gmmastros]

To add a numbers table....

Create Table dbo.Numbers(Num int Constraint PK_Numbers Primary Key (Num))

go

;With Digits as
(
    Select 0 As Num
    Union All Select 1
    Union All Select 2
    Union All Select 3
    Union All Select 4
    Union All Select 5
    Union All Select 6
    Union All Select 7
    Union All Select 8
    Union All Select 9
)
Insert
Into    Numbers(Num)
Select  A.Num * 10000 + B.Num * 1000 + C.Num * 100 + D.Num * 10 + E.Num As Num
From    Digits A
        Cross Join Digits B
        Cross Join Digits C
        Cross Join Digits D
        Cross Join Digits E

Once you have the numbers table, you can use it like this:

Select SubString(Ltrim(SubString(BranchName, Num-1, 1000)), 0, CharIndex(' ', Ltrim(SubString(BranchName, Num-1, 1000))+ ' ')),
       BranchName
From   LOTUS_REP.dbo.GARNADDRESS
       Inner Join Numbers
          On  Len(BranchName) > Numbers.Num - 2
          And SubString(Name, Num-1, 1) = ' '
Order By BranchName, Numbers.Num

This code separates words based on the space character. Your output will be wrong if there are multiple spaces separating your words. Therefore, you should view this as a good starting point, not a finished solution.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Recce]

This is interesting...Thanks George. It works well !

"We know nothing but, that what we know is not the truth..." - Me

 

[gmmastros]

I'm glad it helped.

Don't forget about the double space problem. There's also a problem if there is a space before the data.

For example:

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values(' Beginning Space')
Insert Into @Temp Values('Double  Space')

Select SubString(Ltrim(SubString(Data, Num-1, 1000)), 0, CharIndex(' ', Ltrim(SubString(Data, Num-1, 1000))+ ' ')),
       Data
From   @Temp
       Inner Join Numbers
          On  Len(Data) > Numbers.Num - 2
          And SubString(Data, Num-1, 1) = ' '
Order By Data, Numbers.Num

[tt][blue]
--------------- ---------------
Beginning Beginning Space
Beginning Beginning Space
Space Beginning Space
Double Double Space
Space Double Space
Space Double Space
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Recce]

Thanks, I'll have a look at this ...

"We know nothing but, that what we know is not the truth..." - Me