November 3, 2009 at 10:49 am
Hello I am trying to recreate a process that I had written in Access SQL into TSQL to speed up the process. What I am doing is taking a field that looks as such: Mid-Penn CDC
21 South 12th Street
Philadelphia, PA 19108
Note that it is one field with character return and line feeds separating the different parts of the address.
So far I have been able to pull the name out of the address using this LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1) AS def and it works great. Next I have to be able to pull the second line and then only the ZIP code out. I have a table of zip codes that I am going to run against that field to pull the correct city and state because much of the time there is only a zip code in that field. I believe I can just use the RIGHT function for the zip code and have it pull the last 5 characters of the field but I have no idea how to pull the mailing address.
I have a tried a few things combining LEFT (defendant_info, CHARINDEX(CHAR(13),defendant_info) -1) with another CHARINDEX statement but that is not how it works it would appear. Any help would be much appreciated.
Cheers,
George
November 3, 2009 at 11:23 am
Try this:
create table #T (
ID int identity primary key,
AddressFull varchar(1000));
insert into #T (AddressFull)
select 'Mid-Penn CDC
21 South 12th Street
Philadelphia, PA 19108' union all
select 'Line1
Line2
Line3 12345';
declare @Delimiter char(1);
select @Delimiter = '
';
;with
Numbers (Number) as
(select row_number() over (order by object_id)
from sys.columns),
Split (Sub, Seq, ID) as
(select replace(Split, char(10), ''), Seq, ID
from #T
cross apply
(select substring(AddressFull + @Delimiter, Number, charindex(@Delimiter, AddressFull + @Delimiter, Number) - Number) Split,
row_number() over (order by Number) as Seq
from dbo.Numbers
where Number <= len(AddressFull)
and substring(@Delimiter + AddressFull, Number, 1) = @Delimiter) as Splitter)
select ID, [1] as PlaceName, [2] as Street,
left([3], len([3])-6) as CitySt,
right([3], 5) as Zip
from
(select Sub, Seq, ID
from Split) as Prepivot
pivot
(max(Sub) for Seq in ([1], [2], [3])) as Pvt;
You should be able to modify that pretty easily to use your table and field.
The first bit is just an on-the-fly Numbers table. I recommend building a perment one, if you don't have one already.
The second bit is a standard string parser. It's usually used with commas, but it can take a variety of delimiters. If you use a 2 or more character delimiter (like a hard return), you'll need to replace part of it to clean up the substring.
The third part uses the Pivot operator to turn the pieces back into columns in a row, from rows in a column.
Note that this all assumes you're using SQL 2005, based on the forum selected. If you're actually using SQL 2000, this won't work. SQL 2008 will work just like 2005.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 12:08 pm
Thanks you for a quick response. I do have a few questions though. Is this just adjusting a table with new fields or is it creating a new table? Also what is a "Numbers Table"? I am new to tsql and am not sure what all of that means..even the syntax is quite different than the sql I deal with in Access.
November 3, 2009 at 12:34 pm
Since I don't have a copy of your table, and don't know how it's built, I had to build a table of my own to build the query off of it. You would use your own table, of course, and can ignore the create table and insert statements.
The syntax is new to SQL 2005. The "with <name> as (<query>)" structure is called a Common Table Expression (or CTE) and is a way to build a subquery efficiently.
A Numbers table is just a table of integer numbers. It's usually:
create table dbo.Numbers(Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by s1.object_id)
from sys.columns s1
cross join sys.columns s2;
All that does is use the Row_Number function to generate 10,000 integers, and insert them into a table.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 12:41 pm
Yes it does. What is the purpose of having a table with 10000 integers in it?
November 3, 2009 at 12:43 pm
There are dozens of uses.
Search this site for Tally table or Numbers table, you'll find a number of articles and dozens of threads where they come up.
http://www.simple-talk.com has a workshop on a "Helper Table", which is the same thing.
In this particular case, it's used to separate the string on the return character.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 12:53 pm
george.greiner (11/3/2009)
Yes it does. What is the purpose of having a table with 10000 integers in it?
a Tally/Numbers table lets you leverage the way SQL server uses Set Based operations versus handing items one by one(RBAR-Row By Agonizing Row)
There's a lot of examples here on SSC where using a table of integers is several orders of magnitude faster than other methods.
this is a list of just some of my snippets of code that feature a Tally table; each snippet i've used in one way or another:
Tally Split String to @Table
Tally 2 Dimensional Split
Tally 3 DimentionalSplit
Tally Biweekly Fridays
Tally Build filepath with Date Info and X num of files
Tally CTE Split Example
Tally CTE Strip Extra Whitespace
Tally Calendar Fill with values
Tally CamelCase To Camel Case for Readability
Tally Extract substrings example
Tally Inline Tally select X# of words
Tally Insert item at intervals in string
Tally Parse 3 line address table
Tally Propercase any string
Tally Generate lots of Random Numbers
Tally Reorder items in a string
Tally Select N number of words
Tally Specific substring with decimal in it
Tally Split Cross Apply a table
Tally Split Multi Delimiter
Tally Strip HTML
Tally Strip Numeric
Tally Strip comments from Procs
Tally Strip nonnumeric
Tally custom replace htmlencode
Tally format Currency
Tally format phone number
Tally identify gaps in sequence
Tally Generate password Complexity
Tally Generate range of years formatted
Lowell
November 3, 2009 at 12:56 pm
Ok cool I will definitely be looking into that code later on to better understand and speed up what I am doing! Really appreciate it guys! Thanks.
November 3, 2009 at 2:09 pm
Out of curiosity how do I use CHARINDEX if it can come back null? For instance if there is no CHAR(13) in the field?
November 3, 2009 at 2:47 pm
Either append the character to the end, like the string parser does, or include something in your Where clause about "and charindex(...) > 0".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 6:44 am
Thanks again for the response! I am going to try and use the code you provided but I am getting an error when trying to run it. "Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel."
I did not "create" this server or this particular database for that matter and am not entirely sure how to go about setting a compatibility level to a higher value or the effects of such a move.
Thanks,
George
November 4, 2009 at 12:28 pm
Right-click the database in Management Studio, go to Properties, select Options, and see what the Compatibility Level is set to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2009 at 12:41 pm
The compatibility level is set to 80 which is apparently SQL 2000. I am finding out from the guy who set it up if there is code in there that will be phased out by setting it to 90. Quite frustrating as I figured he would have wrote it in 2005 as he did it just for us but apparently he copied it from my business partners version which is in 2000.
November 4, 2009 at 8:03 pm
george.greiner (11/4/2009)
The compatibility level is set to 80 which is apparently SQL 2000. I am finding out from the guy who set it up if there is code in there that will be phased out by setting it to 90. Quite frustrating as I figured he would have wrote it in 2005 as he did it just for us but apparently he copied it from my business partners version which is in 2000.
Here is a little trick to get you around this particular issue. Change the database that you are using from the database in 80 compatibility mode to one that is 90/100. Now, when you run your query, it will run in the context of that database and will have access to the new functionality.
Here is the key: use three-part naming for all object references, referencing the objects in your original database. Three-part naming will be:
{database name here}.{schema}.{object}
For example, using the Numbers table as an example - you would do the following:
create table {your database}.dbo.Numbers(Number int primary key);
go
insert into {your database}.dbo.Numbers (Number)
select top 10000 row_number() over (order by s1.object_id)
from sys.columns s1
cross join sys.columns s2;
Replace {your database} with the database name you are using and it should work with no problems.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 4, 2009 at 10:28 pm
george.greiner (11/3/2009)
Ok cool I will definitely be looking into that code later on to better understand and speed up what I am doing! Really appreciate it guys! Thanks.
Most of the articles you'll read about Numbers tables will give you examples of things to do with the table... very few will tell you how they actually work so you can come up with your own new methods. The article at the following URL not only gives an example here and there, but also explains how a Tally (or Number) table is used to replace many types of loops. I've never met the author face-to-face but it seems like a fairly good article from what other folks have said 😉
http://qa.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply