June 3, 2015 at 4:50 am
Hi All,
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
Thanks in advance
June 3, 2015 at 5:01 am
Duplicate post. Replies here please: http://qa.sqlservercentral.com/Forums/FindPost1691082.aspx
-- Gianluca Sartori
June 3, 2015 at 5:32 am
Pack_Star (6/3/2015)
Hi All,I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Town) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
Thanks in advance
What is the difference between 'Town' and 'Address' and which is the sample data referring to?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 3, 2015 at 5:35 am
Hi,
Sorry typo on my part. Address/Town are the same column.
The examples are sample records found in the Address Column.
Thanks
June 3, 2015 at 6:02 am
OK. Here is a hack for you.
with addresses
as (select address = 'Elland **REQUIRES BOOKING IN***'
union all
select 'Theale, Nr Reading, Berkshire'
union all
select 'Stockport'
)
select *
,left(address, case when charindex(',', address) = 0 then len(address)
else charindex(',', address) - 1
end) as 'Town Test'
,left(address, case when charindex('*', address) = 0 then len(address)
else charindex('*', address) - 1
end) as 'Town Test2'
,left(address, case when charindex(',', replace(address,'*',',')) = 0 then len(address)
else charindex(',', replace(address,'*',',')) - 1
end) as 'Town Test3'
from addresses;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 3, 2015 at 6:03 am
spaghettidba (6/3/2015)
Duplicate post. Replies here please: http://qa.sqlservercentral.com/Forums/FindPost1691082.aspx
So sorry. I missed this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 3, 2015 at 6:56 am
Hi Phil,
Absolutely perfect!!!
I have been struggling with this for a couple of days.
Many Thanks
June 3, 2015 at 7:08 am
Pack_Star (6/3/2015)
Hi Phil,Absolutely perfect!!!
I have been struggling with this for a couple of days.
Many Thanks
Nigel
That's too long! Post here straight after day 1 in future 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 3, 2015 at 3:19 pm
If you just need whichever comes first, I suggest:
with addresses
as (select address = 'Elland **REQUIRES BOOKING IN***'
union all
select 'Theale, Nr Reading, Berkshire'
union all
select 'Stockport'
)
select address
,left(address, PATINDEX('%[,*]%', address + ',') - 1) as address_trimmed
from addresses;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply