468,316 Members | 2,081 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Split a string where newline, carriage return

code green
1,726 Expert 1GB
I have an address field in a table that contains one, two, three or more lines of an address.
I need to split this into three fields for later insertion into CSV.

I have done something similar with the name field which I don't lile because I am testing the same condition twice
Expand|Select|Wrap|Line Numbers
  1. CASE WHEN CHARINDEX(' ',RTRIM(LTRIM(full_name))) > 0 THEN 
  2. RTRIM(LTRIM(SUBSTRING(full_name,CHARINDEX(' ',full_name)+1,20)))
  3. ELSE RTRIM(LTRIM(full_name)) 
  4. END surname,
  5. CASE WHEN CHARINDEX(' ',RTRIM(LTRIM(full_name))) > 0 THEN 
  6. RTRIM(LTRIM(SUBSTRING(full_name,1,CHARINDEX(' ',full_name)-1)))
  7. ELSE RTRIM(LTRIM(initials))
  8. END    forename,
but the delimiter here was always space.
The address field has carriage return, newline and possibly tab.
How do I split the string where any of these delimiters occur?

(The delimitter is showing as an empty square)
Sep 24 '09 #1
1 14883
Delerna
1,134 Expert 1GB
I think
charindex( fieldtotest,char(13) ) will give the position of carriage return
charindex( fieldtotest,char(10) ) will give the position of line feeds

I can't find anything in my data to test the theory on
Sep 24 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by John Dalberg | last post: by
7 posts views Thread by Nathan | last post: by
4 posts views Thread by Nathan | last post: by
1 post views Thread by con.brady | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.