Hello,

I have a sql 2000 table that has a column with values such as

First Name - George Last Name - Smith Room number - 229 Chart Location
- 7FRH.2
First Name - Mark Last Name - Johnson Room number - 976 Chart Location
- 4FV.5
First Name - Mary Last Name - Wesley Room number - 224 Chart Location
- 8BSS.3
First Name - Lucia Last Name - Guess Room number - 453 Chart Location
- 6BF.1
First Name - Charlotte Last Name - Lopez Room number - 229 Chart
Location - 4VW.5

I need to parse these values into columns. From these columns I only
need the actual data and not the delimiters

the delimeters are:

"First Name - "
" Last Name - "
" Room number - "
" Chart Location - "

Therefore, I need to get 4 columns:

George Smith 229 7FRH.2
Mark Johnson 976 4FV.5
and so on..

Any ideas on how to parse these values into the 4 columns I need?

Thanks!

John Smith (ILCSP (AT) NETZERO (DOT) NET) writes:
Looks like you need to spend some time on reading about the string
functions in T-SQL, particularly charindex and substring.

But unless the fields infomation were machine-generated, there is a
fair chance that not all columns are equally formatted, but you will
need a lot of intelligence in the code to sort this out. In that case,
T-SQL is definitely not the best tool, but I think you are better of
using a client language, preferrably one with good support for regular
expressions.

I don't know who made that table design from the beginning, but the
above is certainly not how you should store data in a relational database.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erl,

Thanks for the reply. I know I need to immerse myself into reading
more SQL and string functions, but right now I need to solve this
problem. I am still trying, but I can't find a solution for it.

John Smith (ILCSP (AT) NETZERO (DOT) NET) writes:
Something on the line of:

SELECT substring (col, charindex(@label1, col) + len(@label1),
charindex(@label2, col) -
(charindex(@label1, col) + len(@label1))
FROM tbl

That is is only one column, repeat for the next.

The above is just an outline. There is a tedious work to test that
all boundaries correct.

And, yes, it's quite boring code to write.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx