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://www.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?
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;
June 3, 2015 at 6:03 am
spaghettidba (6/3/2015)
Duplicate post. Replies here please: http://www.sqlservercentral.com/Forums/FindPost1691082.aspx
So sorry. I missed this.
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 🙂
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply