By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Split a string where newline, carriage return

code green
Expert 100+
P: 1,726
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
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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.