Fun SQL string replace challenge

When I use this code

@stlocation= 3713 Yanceyville st Greensboro NC
 @UseST = NC
REPLACE(@stlocation, @UseST, '')

I want to get
3713 Yanceyville st Greensboro

However-- I get

3713 Yaeyville st Greensboro


It removes the NC not only from the back of the string but also from the STREET!!

Can someone tell me how to remove only from the back of the string? This same thing could happen if a street name is Virginia ST and the state entered is Virginia.. It would take virginia off of both the state and the street!!

LVL 1
cheryl9063Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:
try this

declare @stlocation varchar(100), @UseST varchar(50)

set @stlocation= '201 S Elm Street Greensboro North Carolina'
 set @UseST = 'North Carolina'
 
 select LEFT(@stlocation, DATALENGTH(@stlocation) - DATALENGTH(RTRIM(LTRIM(@UseST))))
0
 
Ephraim WangoyaCommented:


select LEFT(@stlocation, DATALENGTH(@stlocation) - DATALENGTH(@UseST))
0
 
sdstuberConnect With a Mentor Commented:
prepend a space before NC

@UseST = ' ' + 'NC'


even more robust would be to use regular expressions but you'll need udf for that
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
bitrefConnect With a Mentor Commented:
@UseST = ' NC'
0
 
cheryl9063Author Commented:
ewangoya--you were close but when I enter

'201 S Elm Street Greensboro North Carolina'

I get this

201 S Elm Stre

To the others.. This has to be native SQL.. I'm a developer and have no control over add-ins or using regular expressions... Also-- these address can be entered in any form imaginable... commas, no commas, spaces etc.. This is a huge website and I have no control over how the data comes in to me.. I'm tasked with tearing it apart and putting it back together again in a form we can use.. Thanks all!!
0
 
sdstuberCommented:
what about the first part of my suggestion?

prepending a space to whatever value you get

@UseST = ' ' + 'NC'
@UseST = ' ' + 'North Carolina'

@UseST = ' ' + <whatever your input is>

0
 
bitrefConnect With a Mentor Commented:
Then, you may try:
SELECT LEFT(@stlocation, LEN(@stlocation) - LEN(@UseST)) 

Open in new window

0
 
SharathData EngineerCommented:
Do you always have state name at the end? Can you provide more sample data with expectd result?
Is @UseST always NC?
If @stLocation = '201 S Elm Street Greensboro North Carolina', what is the value of @UseST and expected result?
0
 
Anthony PerkinsConnect With a Mentor Commented:
>>This has to be native SQL.. I'm a developer and have no control over add-ins or using regular expressions... <<
Regular expressions are part of "native SQL" in some dialects that is and since you posted in SQL Query Syntax (as well as SQL Server 2008, SQL Server 2005) the responses you are getting reflect that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.