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.
| Author |
Topic |
|
grdrager
Starting Member
2 Posts |
 Posted - 2008-04-01 : 19:58:37
|
| I've found several examples of how to parse a delimited column in a query, but I'm at a bit of a loss in dealing with a column that contains a string of fixed width items(8 char date and (4) 1 char flags)Original Table:ID Lastname Firstname SillyStringtoParse 101 Smith John 20080101AA1120080114AC2220080124AB33 102 Doe Jane 20080202AC34. . . What I need:ID Lastname Firstname Date Flag1 Flag2 Flag3 Flag4 101 Smith John 01-01-2008 A A 1 1 101 Smith John 01-14-2008 A C 2 2 101 Smith John 01-24-2008 A B 3 3 102 Jane Doe 02-02-2008 A C 3 4 . . . Any thoughts would be appreciated. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-01 : 22:31:56
|
| Not really sure what the problem is:Do you already have the columns in a table and just want to parse the fixed length data in one column?select ID, Lastname, Firstname,Date = substring(SillyStringtoParse, 1, 8) ,Flag1 = substring(SillyStringtoParse, 9,1) ,Flag2 = substring(SillyStringtoParse, 10, 1) ,Flag3 = substring(SillyStringtoParse, 11,1) ,Flag4 = substring(SillyStringtoParse, 12,1)If you have everything in a simgle column then it's a lot easier in v2005 but for v2000 you can create a function to split it by the delimiter and then use substring on the fixed length column.If it's a bulk insert then either load into a staging table first or use a format file. ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
grdrager
Starting Member
2 Posts |
Posted - 2008-04-02 : 08:25:26
|
| The original table is from a linked DB4 server. What I would like to achieve is to create a view that would have the desired output which could then be used in add-hoc query tools (Access). I've run across a few examples of parsing output in a single select statement of a delimited-list column. Having the fixed format is easier than delimited since it's simple substrings, but I'm just not sure the best way to achieve this. Sorry if I wasn't clear enough in the original question.Thanks for the reply. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-02 : 08:27:46
|
| Then the query I gave above should be what you want.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
A different kind of parse question