Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Fun SQL string replace challenge

Posted on 2011-03-24
9
Medium Priority
?
475 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 200 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 400 total points
ID: 35209896
@UseST = ' NC'
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 1200 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 400 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 200 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

660 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