469,313 Members | 2,621 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

charindex with error handling?

Hello all,


I am trying to write a script which breaks down a single address field into individual fields, with char(10) or a carriage return as the delimiter.
"empltable" is the table I am using, and "address" is the field I am looking to split.

So far I have managed to:

Split the field into 3

SELECT emplid, name,
CHARINDEX(char(10),address) first_cr,

CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1) second_cr,

LEFT(address, charindex(char(10), address, -1)) as [address1],

SUBSTRING(address, CHARINDEX(char(10), address)+1, CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1)-CHARINDEX(char(10), address)) as [address2],

RIGHT(address,LEN(address)-CHARINDEX(char(10),address,CHARINDEX(char(10),addr ess)+1 )) as [address3]

FROM empltable




...but this only works on the test database, where I am inserting carriage returns into the field to allow the query to work. Basically, when there are less than 2 carriage returns in the address field, the query fails, as there is no error handling in the query.
So, on from that, I tried to introduce ISNULL into the query:

SELECT SUBSTRING(address, 1, NULLIF(CHARINDEX(char(10), address) - 1, -1)) AS [Address1],
SUBSTRING(address, CHARINDEX(char(10), address) + 1, LEN(address)) AS [Address2]
FROM empltable



...This splits the address field into 2. I am having difficulty returning the middle section though without encountering the errors.
Ideally I would rewrite it to handle any number of returns!

Can someone please help!?
Sep 18 '07 #1
7 3525
azimmer
200 Expert 100+
Hello all,


I am trying to write a script which breaks down a single address field into individual fields, with char(10) or a carriage return as the delimiter.
"empltable" is the table I am using, and "address" is the field I am looking to split.

So far I have managed to:

Split the field into 3

SELECT emplid, name,
CHARINDEX(char(10),address) first_cr,

CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1) second_cr,

LEFT(address, charindex(char(10), address, -1)) as [address1],

SUBSTRING(address, CHARINDEX(char(10), address)+1, CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1)-CHARINDEX(char(10), address)) as [address2],

RIGHT(address,LEN(address)-CHARINDEX(char(10),address,CHARINDEX(char(10),addr ess)+1 )) as [address3]

FROM empltable




...but this only works on the test database, where I am inserting carriage returns into the field to allow the query to work. Basically, when there are less than 2 carriage returns in the address field, the query fails, as there is no error handling in the query.
So, on from that, I tried to introduce ISNULL into the query:

SELECT SUBSTRING(address, 1, NULLIF(CHARINDEX(char(10), address) - 1, -1)) AS [Address1],
SUBSTRING(address, CHARINDEX(char(10), address) + 1, LEN(address)) AS [Address2]
FROM empltable



...This splits the address field into 2. I am having difficulty returning the middle section though without encountering the errors.
Ideally I would rewrite it to handle any number of returns!

Can someone please help!?
Question 1: if you have less CRs than fields in the broken up address how do you map the fields?

Question 2: wouldn't it help to first append three CRs to the string then split it up and eventually remove CRs from the end of the last bit?

Question 3: do you absolutely have to do it in one SELECT? Just because it may first help to work it out on several lines (with or without a cursor) and then squeeze it into one SELECT should it be a must.
Sep 18 '07 #2
Question 1: if you have less CRs than fields in the broken up address how do you map the fields?

Question 2: wouldn't it help to first append three CRs to the string then split it up and eventually remove CRs from the end of the last bit?

Question 3: do you absolutely have to do it in one SELECT? Just because it may first help to work it out on several lines (with or without a cursor) and then squeeze it into one SELECT should it be a must.

1. There are not less carriage returns than fields - I do not need to map the postcode to "address 4" for example, I just need to break up the field based on how many carriage returns there are (or at least into 3 parts) There are a few incomplete addresses, and a few empty cells, leaving either no or one carriage return, which is where the error is returning. The mapping is purely based on the position of the carriage return position, and I have the statement to the point where I make the value NULL for "no CR's" in which case the statement runs through fine, until the point where I add in the statement selecting the mid-part (or second portion) of the field, and there is a record with 1 CR...
2. Yes, this would be a lot easier, and to be honest it is the way I did this on the test DB. As things stand, I cannot make changes to the data within the production DB, although the only records causing problems are the ones with missing or incomlpete values anyway.
3. Simply, no. I can do this in multiple select statements if there is an easier way of doing this, but I tried this briefly and still found difficulties with the middle portions of the field. Anything outside of the first portion, either from the left or right of the field, becomes very messy due to all the charindex references I've found, and I'm a bit stumped as to adding error handling to it still...



Basically, I know this may not be the best method of returning the results I need, but it is the only method I have tried where I have been making some headway. My T-SQL is a bit rusty, as I only had to run a few updates and inserts, along with editing a couple of Stored Procedures in my last job.
If anyone knows of a better way of doing this, then please let me know :)

I'd like to get this method working, but if it turns out not to be the best way to do it, then I'd be glad to hear alternatives.

Thanks.
Sep 18 '07 #3
azimmer
200 Expert 100+
1. There are not less carriage returns than fields - I do not need to map the postcode to "address 4" for example, I just need to break up the field based on how many carriage returns there are (or at least into 3 parts) There are a few incomplete addresses, and a few empty cells, leaving either no or one carriage return, which is where the error is returning. The mapping is purely based on the position of the carriage return position, and I have the statement to the point where I make the value NULL for "no CR's" in which case the statement runs through fine, until the point where I add in the statement selecting the mid-part (or second portion) of the field, and there is a record with 1 CR...
2. Yes, this would be a lot easier, and to be honest it is the way I did this on the test DB. As things stand, I cannot make changes to the data within the production DB, although the only records causing problems are the ones with missing or incomlpete values anyway.
3. Simply, no. I can do this in multiple select statements if there is an easier way of doing this, but I tried this briefly and still found difficulties with the middle portions of the field. Anything outside of the first portion, either from the left or right of the field, becomes very messy due to all the charindex references I've found, and I'm a bit stumped as to adding error handling to it still...



Basically, I know this may not be the best method of returning the results I need, but it is the only method I have tried where I have been making some headway. My T-SQL is a bit rusty, as I only had to run a few updates and inserts, along with editing a couple of Stored Procedures in my last job.
If anyone knows of a better way of doing this, then please let me know :)

I'd like to get this method working, but if it turns out not to be the best way to do it, then I'd be glad to hear alternatives.

Thanks.
Here you have a working sample that illustrates the idea. It is possible to do it more intelligently; it is only to show a mindset:
Expand|Select|Wrap|Line Numbers
  1. declare @address varchar(255)
  2. declare @address1 varchar(255), @address2 varchar(255), @address3 varchar(255)
  3. declare @start int, @end int
  4.  
  5. set @address = 'Address Line 1' + char(10)
  6. set @address = @address + 'Address Line 2' + char(10)
  7. set @address = @address + 'Address Line 3' + char(10)
  8. -- select @emplid=emplid, @name=name, @address=@address from empltable
  9.  
  10. set @start = 1
  11.  
  12. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  13. set @address1 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  14. set @start = @end+1
  15.  
  16. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  17. set @address2 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  18. set @start = @end+1
  19.  
  20. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  21. set @address3 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  22. set @start = @end+1
  23.  
  24. print @address
  25. print '->1:'+@address1+'<-'
  26. print '->2:'+@address2+'<-'
  27. print '->3:'+@address3+'<-'
  28.  
Comment out lines 5,6 and 7 and uncomment line 8 to get the actual data from the table. If you comment out lines 6 and/or 7 only you can check the result of an incomplete address set
Sep 18 '07 #4
Here you have a working sample that illustrates the idea. It is possible to do it more intelligently; it is only to show a mindset:
Expand|Select|Wrap|Line Numbers
  1. declare @address varchar(255)
  2. declare @address1 varchar(255), @address2 varchar(255), @address3 varchar(255)
  3. declare @start int, @end int
  4.  
  5. set @address = 'Address Line 1' + char(10)
  6. set @address = @address + 'Address Line 2' + char(10)
  7. set @address = @address + 'Address Line 3' + char(10)
  8. -- select @emplid=emplid, @name=name, @address=@address from empltable
  9.  
  10. set @start = 1
  11.  
  12. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  13. set @address1 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  14. set @start = @end+1
  15.  
  16. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  17. set @address2 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  18. set @start = @end+1
  19.  
  20. set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
  21. set @address3 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
  22. set @start = @end+1
  23.  
  24. print @address
  25. print '->1:'+@address1+'<-'
  26. print '->2:'+@address2+'<-'
  27. print '->3:'+@address3+'<-'
  28.  
Comment out lines 5,6 and 7 and uncomment line 8 to get the actual data from the table. If you comment out lines 6 and/or 7 only you can check the result of an incomplete address set


Thanks a lot for that!
It only seems to work for one record though, and only returns NULL for all values?
I tried to put the CR position into an array as well, but am I right in saying you can't set an array to be a table value?
Sep 19 '07 #5
azimmer
200 Expert 100+
Thanks a lot for that!
It only seems to work for one record though, and only returns NULL for all values?
I tried to put the CR position into an array as well, but am I right in saying you can't set an array to be a table value?
The example works for one record. You either put the whole story into a function and apply that to each row (in a SELECT) or define a cursor and process each row in a cycle.
Sep 19 '07 #6
azimmer
200 Expert 100+
The example works for one record. You either put the whole story into a function and apply that to each row (in a SELECT) or define a cursor and process each row in a cycle.
I don't know if you still follow this thread but here you have a function you can use:
Expand|Select|Wrap|Line Numbers
  1. create function nth_substring(@string varchar(255), @n int)
  2. returns varchar(255)
  3. as
  4. begin
  5. declare @start int, @end int, @section int
  6.  
  7. set @start = 1
  8. set @section = 1
  9. while (@start > 0) and (@section<@n)
  10.     begin
  11.         set @start=charindex(char(10),@string,@start)
  12.         if (@start > 0)
  13.             begin
  14.                 set @section=@section+1
  15.                 set @start=@start+1
  16.             end
  17.     end
  18. if (@section=@n)
  19.     begin
  20.         set @end=charindex(char(10),@string,@start)
  21.         if (@end=0)
  22.             set @end=len(@string)+1
  23.         return substring(@string,@start,@end-@start)
  24.     end
  25.  
  26. return NULL
  27. end
  28. go
  29.  
Use it like this:
Expand|Select|Wrap|Line Numbers
  1. declare @s varchar(255)
  2.  
  3. set @s='Line1'
  4. set @s=@s+char(10)+'Line2'
  5. set @s=@s+char(10)+'Line3'
  6.  
  7. print dbo.nth_substring(@s,2)
  8.  
or like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ..., dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) 
  2. FROM ...
  3.  
Sep 19 '07 #7
I don't know if you still follow this thread but here you have a function you can use:
Expand|Select|Wrap|Line Numbers
  1. create function nth_substring(@string varchar(255), @n int)
  2. returns varchar(255)
  3. as
  4. begin
  5. declare @start int, @end int, @section int
  6.  
  7. set @start = 1
  8. set @section = 1
  9. while (@start > 0) and (@section<@n)
  10.     begin
  11.         set @start=charindex(char(10),@string,@start)
  12.         if (@start > 0)
  13.             begin
  14.                 set @section=@section+1
  15.                 set @start=@start+1
  16.             end
  17.     end
  18. if (@section=@n)
  19.     begin
  20.         set @end=charindex(char(10),@string,@start)
  21.         if (@end=0)
  22.             set @end=len(@string)+1
  23.         return substring(@string,@start,@end-@start)
  24.     end
  25.  
  26. return NULL
  27. end
  28. go
  29.  
Use it like this:
Expand|Select|Wrap|Line Numbers
  1. declare @s varchar(255)
  2.  
  3. set @s='Line1'
  4. set @s=@s+char(10)+'Line2'
  5. set @s=@s+char(10)+'Line3'
  6.  
  7. print dbo.nth_substring(@s,2)
  8.  
or like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ..., dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) 
  2. FROM ...
  3.  

Awesome!
Thanks a lot azimmer! :)

I was going to go for the method of adding in CR's in the end, but this works great.

Much appreciated.
Sep 20 '07 #8

Post your reply

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

Similar topics

5 posts views Thread by M Wells | last post: by
2 posts views Thread by Little PussyCat | last post: by
5 posts views Thread by Willem | last post: by
3 posts views Thread by csomberg | last post: by
1 post views Thread by jeremy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.