Help with REPLACE please!!!!

Question

Post reply

Help with REPLACE please!!!!

skeeter042

Valued Member

Points: 69
More actions
April 12, 2010 at 6:04 am

#220377

I'm having issues with REPLACE and I was hoping somebody could help me out?
Heres an example of my sql:
replace(billtoaddress1, 'PTE I', 'PT')
replace(billtoaddress1, 'PT # II', 'PT')
replace(billtoaddress1, 'PTE II', 'PT')
So as you see, my intention was to shorten PTE I to just PT etc etc.
But I can not figure out how to drop the unwanted "space". or multiple "spaces"
For example:
replace(billtoaddress1, 'PTE I', 'PT') only changes it to PT I
or
replace(billtoaddress1, 'PT # II', 'PT') does not change at all.
Any suggestions on how I can get this to return my desired results?
Thanks!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

hi,

Use the stuff function to replace.....

if the occurence is the end of string only.........

SELECT STUFF(Column_Name, CHAINDEX('P',Column_Name), LEN(Column_Name), 'PT');

otherwise....

U should write some logic using SQL String functions ....

🙂

skeeter042 Valued Member Points: 69 More actions · Answer 2

Sorry, I'm not following you. :unsure: Can you give me another example after looking at all of my original code?

select rowid, accountnumber, tenantnumber, accountname, serviceaddress, billtoaddress1,

case

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE I', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'POINTE', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT #I', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE II', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT # II', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE III', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT # III', 'PT')

when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'POINTE1', 'PT')

end

as new_billtoaddress from ubaccount where billtoaddress1 like '%Brentwood PT%'

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

(billtoaddress1, 'PTE I', 'PT')

replace(billtoaddress1, 'POINTE', 'PT')

replace(billtoaddress1, 'PT #I', 'PT')

replace(billtoaddress1, 'PTE II', 'PT')

replace(billtoaddress1, 'PT # II', 'PT')

replace(billtoaddress1, 'PTE III', 'PT')

replace(billtoaddress1, 'PT # III', 'PT')

replace(billtoaddress1, 'POINTE1', 'PT')

I am asking U that the PT,PTE I,POINTE,PTE II etc....

all these words ocuured at end of the string only u should go for STUFF function....

i,e

Brentwood PT

Brentwood PTE

Brentwood PT I

Brentwood PTE II

Brentwood POINTE1

if Ur data existed like the above u can use the STUFF as in the above reply..

If the above is not Ur data.........

The following is worked with little bit logic

Use PATINDEX to findout the Frame[%PT% or %PTE I% %POINTE1%] exactly...& then u can replace the PT

try the above one....

Lowell SSC Guru Points: 323547 More actions · Answer 4

some of it is your order of operations:

'PTE III'

you are replacing this first:

replace(billtoaddress1, 'PTE I', 'PT')

replace(billtoaddress1, 'PTE II', 'PT')

replace(billtoaddress1, 'PTE III', 'PT')

so the end result is PTEII

that's not what you wanted. That's the reason for the suggestion to stuff everything right of PT with an empty string...fix them all.

otherwise you have to replace the longest string first

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

skeeter042 Valued Member Points: 69 More actions · Answer 5

Yes, sorry, all of the words occur at the end of the string. I'm a sql newbie, so I'm afraid I do no completly understand the syntax of the STUFF function as you used in your example.

SELECT STUFF(Column_Name, CHAINDEX('P',Column_Name), LEN(Column_Name), 'PT');

Column_Name in this case would be billtoaddress1?

And CHAINDEX? Is that correct? Or was it your intention to use CHARINDEX instead?

skeeter042 Valued Member Points: 69 More actions · Answer 6

I figured it out and it works beautifully! Thanks so much for your help!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

skeeter042 (4/12/2010)
Sorry, I'm not following you. :unsure: Can you give me another example after looking at all of my original code?
select rowid, accountnumber, tenantnumber, accountname, serviceaddress, billtoaddress1,
case
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE I', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'POINTE', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT #I', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE II', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT # II', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PTE III', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'PT # III', 'PT')
when billtoaddress1 like '%Brentwood PT%' then replace(billtoaddress1, 'POINTE1', 'PT')
end
as new_billtoaddress from ubaccount where billtoaddress1 like '%Brentwood PT%'

This won't work, if you want to REPLACE the same expression with several alternatives, then you must nest the REPLACE, something like this:

CREATE TABLE #ubaccount (

rowid INT IDENTITY(1,1),

accountnumber INT,

tenantnumber INT,

accountname VARCHAR(20),

serviceaddress VARCHAR(30),

billtoaddress1 VARCHAR(30))

INSERT INTO #ubaccount (

accountnumber,

tenantnumber,

accountname,

serviceaddress,

billtoaddress1)

select 101, 10, '', '', billtoaddress1 = 'A Brentwood PTE' UNION ALL

select 102, 10, '', '', billtoaddress1 = 'B Brentwood PTE I' UNION ALL

select 103, 10, '', '', billtoaddress1 = 'C Brentwood POINTE' UNION ALL -- < WILL BE FILTERED OUT BY WHERE CLAUSE

select 104, 10, '', '', billtoaddress1 = 'D Brentwood PT #I' UNION ALL

select 105, 10, '', '', billtoaddress1 = 'E Brentwood PTE II' UNION ALL

select 106, 10, '', '', billtoaddress1 = 'F Brentwood PT # II' UNION ALL

select 107, 10, '', '', billtoaddress1 = 'G Brentwood PTE III' UNION ALL

select 108, 10, '', '', billtoaddress1 = 'H Brentwood PT # III' UNION ALL

select 109, 10, '', '', billtoaddress1 = 'I Brentwood POINTE1' -- <WILL BE FILTERED OUT BY WHERE CLAUSE

SELECT *,

WrongWay = REPLACE(REPLACE(REPLACE(billtoaddress1,'PT I', 'PT'),'PT II', 'PT'),'PTE III', 'PT'),

RightWay = REPLACE(REPLACE(REPLACE(billtoaddress1,'PTE III', 'PT'),'PTE II', 'PT'),'PTE I', 'PT')

FROM #ubaccount

WHERE billtoaddress1 LIKE '%Brentwood PT%'

As Lowell pointed out, the sequence is important - compare the two columns Wrongway and Rightway in the example.

It's more likely that STUFF or LEFT will work well in this context. Try this:

SELECT *,

charindex('Brentwood PT', billtoaddress1),

charindex('Brentwood PT', billtoaddress1) + LEN('Brentwood PT'),

LEFT(billtoaddress1, charindex('Brentwood PT', billtoaddress1) + LEN('Brentwood PT')-1)

FROM #ubaccount

WHERE billtoaddress1 LIKE '%Brentwood PT%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

skeeter042 Valued Member Points: 69 More actions · Answer 8

Awesome! You guys rock! Thanks again to all who helped out!