Solved

Fun SQL string replace challenge

Posted on 2011-03-24
9
471 Views
Last Modified: 2012-05-11
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!!

0
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35209785


select LEFT(@stlocation, DATALENGTH(@stlocation) - DATALENGTH(@UseST))
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 50 total points
ID: 35209788
prepend a space before NC

@UseST = ' ' + 'NC'


even more robust would be to use regular expressions but you'll need udf for that
0
 
LVL 5

Assisted Solution

by:bitref
bitref earned 100 total points
ID: 35209896
@UseST = ' NC'
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 1

Author Comment

by:cheryl9063
ID: 35209970
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35210066
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 300 total points
ID: 35210123
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
 
LVL 5

Assisted Solution

by:bitref
bitref earned 100 total points
ID: 35210130
Then, you may try:
SELECT LEFT(@stlocation, LEN(@stlocation) - LEN(@UseST)) 

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35211086
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 35214997
>>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

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question