Solved

Fun SQL string replace challenge

Posted on 2011-03-24
9
463 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
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35209785


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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 73

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:
ewangoya 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 40

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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