CharIndex Manipulation (SQL 2012). Select Case Statement to remove Part of String after one or two specific characters

Question

Post reply

CharIndex Manipulation (SQL 2012). Select Case Statement to remove Part of String after one or two specific characters

Pack_Star

Mr or Mrs. 500

Points: 547
More actions
June 3, 2015 at 4:50 am

#317894

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Duplicate post. Replies here please: http://www.sqlservercentral.com/Forums/FindPost1691082.aspx

-- Gianluca Sartori

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 3

Hi,

Sorry typo on my part. Address/Town are the same column.

The examples are sample records found in the Address Column.

Thanks

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

spaghettidba (6/3/2015)
Duplicate post. Replies here please: http://www.sqlservercentral.com/Forums/FindPost1691082.aspx

So sorry. I missed this.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Pack_Star Mr or Mrs. 500 Points: 547 More actions · Answer 6

Hi Phil,

Absolutely perfect!!!

I have been struggling with this for a couple of days.

Many Thanks

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

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 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ScottPletcher SSC Guru Points: 101134 More actions · Answer 8

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".