charindex with error handling?

[iritchie]

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(addre ss, CHARINDEX(char( 10), address)+1, CHARINDEX(char( 10), address, CHARINDEX(char( 10), address)+1)-CHARINDEX(char( 10), address)) as [address2],

RIGHT(address,L EN(address)-CHARINDEX(char( 10),address,CHA RINDEX(char(10) ,address)+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(addre ss, 1, NULLIF(CHARINDE X(char(10), address) - 1, -1)) AS [Address1],
SUBSTRING(addre ss, 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!?

[azimmer]

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(addre ss, CHARINDEX(char( 10), address)+1, CHARINDEX(char( 10), address, CHARINDEX(char( 10), address)+1)-CHARINDEX(char( 10), address)) as [address2],

RIGHT(address,L EN(address)-CHARINDEX(char( 10),address,CHA RINDEX(char(10) ,address)+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(addre ss, 1, NULLIF(CHARINDE X(char(10), address) - 1, -1)) AS [Address1],
SUBSTRING(addre ss, 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.

[iritchie]

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.

[azimmer]

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:

declare @address varchar(255)
declare @address1 varchar(255), @address2 varchar(255), @address3 varchar(255)
declare @start int, @end int
 
set @address = 'Address Line 1' + char(10)
set @address = @address + 'Address Line 2' + char(10)
set @address = @address + 'Address Line 3' + char(10)
-- select @emplid=emplid, @name=name, @address=@address from empltable
 
set @start = 1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address1 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address2 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address3 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
print @address
print '->1:'+@address1+'<-'
print '->2:'+@address2+'<-'
print '->3:'+@address3+'<-'
 

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

[iritchie]

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:

declare @address varchar(255)
declare @address1 varchar(255), @address2 varchar(255), @address3 varchar(255)
declare @start int, @end int
 
set @address = 'Address Line 1' + char(10)
set @address = @address + 'Address Line 2' + char(10)
set @address = @address + 'Address Line 3' + char(10)
-- select @emplid=emplid, @name=name, @address=@address from empltable
 
set @start = 1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address1 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address2 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
set @end = case when charindex(char(10),@address,@start)>0 then charindex(char(10),@address,@start) else len(@address) end
set @address3 = case when @end-@start>=0 then substring(@address, @start, @end-@start) else null end
set @start = @end+1
 
print @address
print '->1:'+@address1+'<-'
print '->2:'+@address2+'<-'
print '->3:'+@address3+'<-'
 

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?

[azimmer]

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.

[azimmer]

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:

create function nth_substring(@string varchar(255), @n int)
returns varchar(255)
as
begin
declare @start int, @end int, @section int
 
set @start = 1
set @section = 1
while (@start > 0) and (@section<@n)
    begin
        set @start=charindex(char(10),@string,@start)
        if (@start > 0)
            begin
                set @section=@section+1
                set @start=@start+1
            end
    end
if (@section=@n)
    begin
        set @end=charindex(char(10),@string,@start)
        if (@end=0)
            set @end=len(@string)+1
        return substring(@string,@start,@end-@start)
    end
 
return NULL
end
go
 

Use it like this:

declare @s varchar(255)
 
set @s='Line1'
set @s=@s+char(10)+'Line2'
set @s=@s+char(10)+'Line3'
 
print dbo.nth_substring(@s,2)
 

or like this:

SELECT ..., dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) 
FROM ...
 

[iritchie]

I don't know if you still follow this thread but here you have a function you can use:

create function nth_substring(@string varchar(255), @n int)
returns varchar(255)
as
begin
declare @start int, @end int, @section int
 
set @start = 1
set @section = 1
while (@start > 0) and (@section<@n)
    begin
        set @start=charindex(char(10),@string,@start)
        if (@start > 0)
            begin
                set @section=@section+1
                set @start=@start+1
            end
    end
if (@section=@n)
    begin
        set @end=charindex(char(10),@string,@start)
        if (@end=0)
            set @end=len(@string)+1
        return substring(@string,@start,@end-@start)
    end
 
return NULL
end
go
 

Use it like this:

declare @s varchar(255)
 
set @s='Line1'
set @s=@s+char(10)+'Line2'
set @s=@s+char(10)+'Line3'
 
print dbo.nth_substring(@s,2)
 

or like this:

SELECT ..., dbo.nth_substring(address,1), dbo.nth_substring(address,2), dbo.nth_substring(address,3) 
FROM ...
 

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.