March 19, 2008 at 3:42 pm
I have a table that has a field called Master_Address that contains both a city and a state (such as Omaha NE). All I want to do is to delete the right 2 characters no matter what they are (NE, MN, IA, etc) and leave the city name. Is there an easy way to trim the right 2 characters?
I can do this in either SQL 2005 or Access-whichever may be easier.
Thanks!
March 19, 2008 at 3:47 pm
Off the top of my head:
Master_Address = Substring(Master_Address, Len(Master_Address)-2)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 4:43 pm
one thing to think about is stripping the last two char's leaves the right most space still there...that said, here is the code
CREATE TABLE citystate ( col1 VARCHAR(20) )
INSERT INTO citystate
VALUES ( 'new prague mn' )
INSERT INTO [citystate]
VALUES ( 'amies IA' )
SELECT *,
SUBSTRING(col1, 1, LEN(col1) - 2)
FROM citystate
-- Cory
March 19, 2008 at 4:52 pm
Rats! Cory has it right, my answer is wrong...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 4:53 pm
Corrected:
rbarryyoung (3/19/2008)
Off the top of my head:
Master_Address = Substring(Master_Address, 1, Len(Master_Address)-2)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 5:03 pm
You can also use the left function.
Master_Address =
RTRIM(LEFT(Master_Address, Len(Master_Address)-2)
March 19, 2008 at 7:16 pm
Right. Clearly I was typing SUBSTRING and thinking LEFT.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply