| Author |
Topic |
|
lemondash
Posting Yak Master
159 Posts |
 Posted - 2007-02-22 : 09:38:57
|
| Afternoon,Have a small select query i need help with writing please, i have a table called TBLuserdetails and the problem is with a colunm inside it called sDepartment.The problem is the data is being stored incorrectly which i will fix at a later date. Currently the data is being stored like this, here is an example "dataitem1|dataitem2|dataitem3".It has three data items which are being slipt up with a "|", what i what to do is write a select statment from thi stable and slipt the colunm up so that the results are in three colunm's.Please help, thank you in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-22 : 09:43:52
|
With the help of this [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033[/url] function,SELECT dbo.fnParseString(3, '|', sDepartment) AS Col1, dbo.fnParseString(2, '|', sDepartment) AS Col2, dbo.fnParseString(1, '|', sDepartment) AS Col3FROM TBLUserDetails Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 09:48:25
|
| [code]SELECT [COLUMN_1] = substring(MyCol + '|', 0 + 1, charindex('|', MyCol + '|', 0 + 1) - 0 - 1 ), [COLUMN_2] = substring(MyCol + '|', charindex('|', MyCol + '|') + 1, charindex('|', MyCol + '|', charindex('|', MyCol + '|') + 1) - charindex('|', MyCol + '|') - 1 ), [COLUMN_3] = substring(MyCol + '|', charindex('|', MyCol + '|', charindex('|', MyCol + '|') + 1) + 1, charindex('|', MyCol + '|', charindex('|', MyCol + '|', charindex('|', MyCol + '|') + 1) + 1) - charindex('|', MyCol + '|', charindex('|', MyCol + '|') + 1) - 1 )FROM dbo.MyTable[/code]My preference would be to put the "delimited column" into a TEMP table, with 3 new empty columns, and split the data in-situ in that table, and then use Update to put it back into the original table.[code]DECLARE @I1 int, @I2 int, @I3 intUPDATE USET @I1 = CHARINDEX('|', MyCol + '|') , [COLUMN_1] = LEFT(MyCol, @I1-1) , @I2 = NullIf(CHARINDEX('|', MyCol + '|', @I1+1), 0) , [COLUMN_2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1) , @I3 = NullIf(CHARINDEX('|', MyCol + '|', @I2+1), 0) , [COLUMN_3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)FROM dbo.MyTable AS U[/code]Kristen |
|
|
|
lemondash
Posting Yak Master
159 Posts |
Posted - 2007-02-22 : 10:15:17
|
| cheers you are a star, thank you so much !!!!!can you give me some advice. I find books online and the sql help files hard to follow.Can you recommend any good books that would help me write query that are more complexed. cheers once again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 10:51:54
|
| You could do worse than just reading the posts on SQL Team ... |
|
|
|
|
|
|
select query help.