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!?
7 3978
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.
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.
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
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?
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.
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 ...
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: M Wells |
last post by:
Hi All,
Just wondering if it's any more efficient to use the following SQL
statement to return a partial match from a column as opposed to the
second statement.
SELECT * FROM Table1 WHERE...
|
by: Little PussyCat |
last post by:
Hello,
I need to be able to replace only the first occurance of a space character
in a column.
Reason being is the data in the column I am trying to replace seems to have
umpteen space...
|
by: Willem |
last post by:
Hello
I am quite hopeless and of course a newbe.
The situation: Sql2k / query
I would like it ot break down the following string:
2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094
|
by: csomberg |
last post by:
SQL Server 2000
Ya know, it is always the simplest stuff that gets ya !!
I am having the hardest time getting a simple piece of code working.
Must be brain dead today.
Goal: Get the users...
|
by: prav78 |
last post by:
hi there
i have a field name(fil_srt_cond) with the values of
cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3
i used the char index:-
...
|
by: db55 |
last post by:
This script doesn't work. Why?
UPDATE
SET = LTRIM(SUBSTRING(, 1,
convert(bigint, CHARINDEX(',', Comments)-1)))
WHERE NOT( IS NULL) AND LEN() > 8
Basically, I'm trying to...
|
by: Laura-Jayne |
last post by:
Join Date: May 2006
Posts: 6 Help - SQL Query Anaylzer
--------------------------------------------------------------------------------
Hi
I am using the following code to give me...
|
by: matthewwhaley |
last post by:
What is the best way to essentially use the charindex(find) function
if the value is could be more than one variable (A or B or C)
I can't seem to get an "or", "if" or "select if" to work
...
|
by: jeremy |
last post by:
Hello,
I was hoping someone could help me with this SQL statement.
The column 'options.optionDescrip' is a varchar field.
Some values of the 'options.optionDescrip' contain commas, some do...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |