Prev: Open a Windows Form from SSRS
Next: Monitoring UPDATE
From: shank on 26 Jun 2010 14:45
I have a SHIPTO field that includes name and address separated with carriage
returns. Using CHARINDEX I tested for Char(13) and Char(10) and they are
intact. How do I parse that field into separate fields when I don't know how
many instances of CHAR(13)CHAR(10) there are? I will have FullName, Add1,
Add2, and maybe Add3, then city, state, zip. There's no separator between
city and state, but luckily all states will be 2 characters. Is there an
easy way to do this?

JOHN DOE CHAR(13)CHAR(10) 1785 ELM STREET CHAR(13)CHAR(10) WICHITA KS
84078-9340

thanks!


From: Erland Sommarskog on 26 Jun 2010 18:29
shank (shank(a)tampabay.rr.com) writes:
> I have a SHIPTO field that includes name and address separated with
> carriage returns. Using CHARINDEX I tested for Char(13) and Char(10) and
> they are intact. How do I parse that field into separate fields when I
> don't know how many instances of CHAR(13)CHAR(10) there are? I will have
> FullName, Add1, Add2, and maybe Add3, then city, state, zip. There's no
> separator between city and state, but luckily all states will be 2
> characters. Is there an easy way to do this?
>
> JOHN DOE CHAR(13)CHAR(10) 1785 ELM STREET CHAR(13)CHAR(10) WICHITA KS
> 84078-9340

On http://www.sommarskog.se/arrays-in-sql-2005.html I have several
methods to crack a delimited list into a table. Make sure that you
use one that returns the list position. In your example, you will
have:

1 JOHN DOE
2 1785 ELM STREET
3 WICHITA KS 84078-9340

But another address may yield

1 Sture Pettersson
2 c/o Andersson
3 Lingonstigen 12
4 67733 Blaskhult
5 SWEDEN

OK, maybe you can disregard that particular case, if all addresses are
are in the US and well-formed, but you get the drift. You have to
inspect each field and use heuristics to see what you have.

T-SQL is a lousy tool for this; I would strongly recommend that you
implement the heuristics in a stored procedure written in C# or VB .Net,
or do it client-side. (And in that case you just as well crack the
string in the same place as well.)


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Open a Windows Form from SSRS
Next: Monitoring UPDATE