substrings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

substrings

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 09:03:54

i have a column representing "street" addresses which currently has a variety of information squashed into it, including quadrant (NSWE), housenu, street, and apt no.). I would like to start by extracting a substring of that field (STREET) when the first character is a 'N', 'S', 'E', or 'W' and only if that character is standing alone and followed by a space. My assumption would be that it would look something like Update Arr_per set quad = substring(street,1,1) when substring(street,1,1)='N'. But something tells me i'm on the wrong track.

suggestions?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:13:47

start by working out how to find only the rows you are interested in.

Probably something like


SELECT <columns>
FROM <Table>
WHERE
    <column> LIKE '[NSEW] %'

Which should find any rows where the column starts with N,S,E,W and then has a space immediately after.

Then I'm a bit lost for what you want. so some sample data would be good there.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 09:23:13

Thanks. but that worked fine when i tweaked it thusly:
update ARR_PER
set QUAD = SUBSTRING(street,1,1) where STREET like '[NSEW]%'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:28:20

ok. cool.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 09:31:10

Now it gets really tricky. Moving back to that STREET column, if i use the following:
SELECT Street
FROM arr_per
WHERE
Street LIKE '[0123456789]%'
i get a good set of results which show me all the entries that are preceded by numbers. In every case, these numbers represent the HOUSENU and i would like those values (from 1 to 5 characters) copied to the HOUSENU field. So in the above select, where i see '111 Petus street' in the STREET field, i would like the '111' copied to the 'HOUSENU' column for that row. Additionally, where i see 1245 Canal Street in the STREET field, i would like the '1245' copied to the housenu field in that row. In every case, the length of the substring will change, so i doubt using 'substring' will work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 09:35:31

[code]
SELECT LEFT(Street,CASE WHEN CHARINDEX(' ',Street)> 0 THEN CHARINDEX(' ',Street) ELSE PATINDEX('%[A-Za-z]%',Street) END -1) AS HouseNu
FROM arr_per
WHERE
Street LIKE '[0-9]%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 09:38:47

i have a less than graceful way to do this. I can take the results of that select:
Street QUAD uniquekey
111 PETTUS STREET NULL 0{{v|iAGL4HuuPGVPuU3h9
503 S STEWART ST NULL 0{1KOsZq19|xwEtuKR4OW1
1256 CANAL DRIVE NULL 0{32hb8qDFlwZW4KZu4AlU
370 BROAD AVENUE NULL 0{D53yxVj9{vRhOfM0t9WH
918 E. 17TH AVE NULL 0 {ijPmqUH8DwX7wibUsxyW

Including the uniquekey value for each row, export that to excel. Parse the street column according to a space delimited format, create a concatenate column in excel which states =Concatenate("update arr_per set quad=' ",A1," where uniquekey = ' ",C1")
And copy those values down, paste that into a query, etc.

i would love to do it all in sql, but....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:48:12

you might want to give this a whirl. It's amazing.
http://code.google.com/p/google-refine/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 15:01:13

Thanks visakh. I will study your solution more so i can understand what it's doing. i would prefer to get out of the habit of hacking data out to excel in order to manipulate it. Old habits die hard.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 15:42:13

yep...let me know if you face any issues in this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 16:42:11

Thanks. I've modified to match my table name and columns:
SELECT LEFT(addressline1,CASE WHEN CHARINDEX(' ',addressline1)> 0 THEN CHARINDEX(' ',addressline1) ELSE PATINDEX('%[A-Za-z]%',addressline1) END -1) AS HouseNu
FROM persondata
WHERE
addressline1 LIKE '[0-9]%'

I am getting the following error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

I wonder if it is intended to fire off correctly assuming the substring value (NSEW) has actually been removed from the original field? If so, it won't, because my use of the substring query, while it puts that character in the quad field, also leaves it intact in the originating field (which is not really what i wanted it to do - whole other issue).
or is that not the nature of this error at all?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 18:02:57

does it address line always have alphabetical data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-23 : 19:42:17

It's a varchar(50) and always has a mix of alpha and numerical data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 21:00:30

quote:
Originally posted by WJHamel

It's a varchar(50) and always has a mix of alpha and numerical data.

then its fine. Also I hope it has spaces seperating the numeric and alphabetical part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-24 : 09:20:58

it does. But i noticed that i get a different error when i change the value located here: addressline1) END -1) to anything greater than -1 (0 or above). The error then reports: 'Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '0'.'. any other negative value produces the previous error. Otherwise i can't seem to get the script to do anything else.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:02:02

quote:
Originally posted by WJHamel

it does. But i noticed that i get a different error when i change the value located here: addressline1) END -1) to anything greater than -1 (0 or above). The error then reports: 'Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '0'.'. any other negative value produces the previous error. Otherwise i can't seem to get the script to do anything else.

you dont need to put explicit 0 there. if you dont want to add anything just remove -1 part. then it should work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-24 : 11:44:12

thank you, absolutely perfect. this is going to save me time and frustration.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 12:41:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-29 : 10:04:30

Ok, the script in this thread worked very well in my current project for pulling the first numeric values from the Addressline1 column in my table and allowing me to insert those values into the "housenu" column accordingly. The modifications to that script looked thusly:
update mnione set housenuone =LEFT(addressline1,CASE WHEN CHARINDEX(' ',addressline1)> 0 THEN CHARINDEX(' ',addressline1) ELSE PATINDEX('%[A-Za-z]%',addressline1) END)
WHERE addressline1 LIKE '[0-9]%'

I then updated Housenu based on the values in Housnuone.

Now, i'm trying to get my brain around further modifications to the script to take another look at the AddressLine1 column and updating the "Street" column to hold the remaining values in addressline1, assuming the numeric value at the start of each string were actually removed. So, in a case where the value of Addressline1 is "5638 PEACHTREE RD", the "5638" portion is now in the Housenu column. The next stop is dropping "PEACHTREE RD" into the "Street" column using the above script.

Is it worth trying to modify the above to get from point a to point b, or is there another approach i should be taking? I'm imagining an update to Street with a substring of Addressline1 but with conditions to ignore the initial numerical values before the first ' ' in the string.

thanks

james

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 11:46:23

are you sure that Addressline 1 will always have only HouseNu and street portion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-29 : 12:05:57

i'm sure they will not.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources