Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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!!

0
cheryl9063
Asked:
cheryl9063
  • 2
  • 2
  • 2
  • +3
5 Solutions
 
Ephraim WangoyaCommented:


select LEFT(@stlocation, DATALENGTH(@stlocation) - DATALENGTH(@UseST))
0
 
sdstuberCommented:
prepend a space before NC

@UseST = ' ' + 'NC'


even more robust would be to use regular expressions but you'll need udf for that
0
 
bitrefCommented:
@UseST = ' NC'
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
Ephraim WangoyaCommented:
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
 
bitrefCommented:
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 PerkinsCommented:
>>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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now