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

Transact-SQL (2005)

String analysis

Author

Topic

davidc
Starting Member

26 Posts

Posted - 2010-01-14 : 06:41:52

I have a table containing a suppliernumber column and a suppliername column. I need to create a table which contains the suppliernumber with each word of the name in a separate row along with the word number. EG

SuppNbr Word WordNbr
1001 John 1
1001 Smith 2
1001 Electrical 3
1001 Company 4
1002 Browns 1
1002 Plumbing 2
1002 Supplies 3

How can I do this from the input table containing multiple suppliers similar to the one below?
SuppNbr SuppName
1001 John Smith Electrical Company
1002 Browns Plumbing Supplies

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 06:48:36

SELECT SuppNbr,
LTRIM(RTRIM(MAX(CASE WHEN WordNbr = 1 THEN Word ELSE ' ' END)
+ MAX(CASE WHEN WordNbr = 2 THEN Word ELSE ' ' END)
+ MAX(CASE WHEN WordNbr = 3 THEN Word ELSE ' ' END)
+ MAX(CASE WHEN WordNbr = 4 THEN Word ELSE ' ' END)
))
FROM Table1
GROUP BY SuppNbr

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

davidc
Starting Member

26 Posts

Posted - 2010-01-14 : 06:51:46

This will do the reverse of what I need. I want to create the first table from the second in my example.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 08:37:18

[code]DECLARE @Sample TABLE
(
SuppNbr INT,
SuppName VARCHAR(8000)
)

INSERT @Sample
(
SuppNbr,
SuppName
)
SELECT 1001, 'John Smith Electrical Company' UNION ALL
SELECT 1002, 'Browns Plumbing Supplies'

SELECT s.SuppNbr,
f.Data AS Word,
f.RowID AS WordNbr
FROM @Sample AS s
CROSS APPLY dbo.fnParseList(' ', s.SuppName) AS f[/code]See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 for fnParseList function.

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

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-14 : 08:43:57

Peso, I tried your code just to see what was going on and got an error at dbo.fnParseList is an invalid object name, would it exist in another location? (or would I have to call it more specifically)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-14 : 09:26:41

You could follow the link above, copy the function code and run on your database, and try again.

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

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-14 : 09:42:31

Ah thanks, appreciate it, still early, missed the link!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources