Get all characters left of string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Get all characters left of string

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2013-03-19 : 14:10:04

Hi all,

I need to query a table column and retrieve all characters left of the string, 'ECSO'. Some of the fields don't contain it though. If it doesn't occur, I will just return whatever is there. I imagine some combination of LEFT and/or CHARINDEX is the solution.

The table is intel_request, and the column is reqofficer. Sample data looks like this:

SMITH, JOHNECSO9999
DOE, JOHNECSO1029
OTHER
THOMAS, FRANKECSO1092

select ?
from intel_request

Thanks so much for any help you can provide.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-03-19 : 14:22:29

PATINDEX and CASE (to handle if ECSO isn't in the string)

DECLARE @str varchar(100) = 'THOMAS, FRANKECSO1092'

SELECT CASE WHEN PATINDEX('%ECSO%',@str) > 0
THEN SUBSTRING(@str,1,PATINDEX('%ECSO%',@str)-1)
ELSE @str
END

Jim

Everyday I learn something that somebody else already knew

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-19 : 14:29:27

[code]
select case when charindex('ECSO', reqofficer) > 0 then left(reqofficer, charindex('ECSO', reqofficer)-1)
else reqofficer
end
from intel_request
[/code]

Be One with the Optimizer
TG

fralo
Posting Yak Master

161 Posts

Posted - 2013-03-19 : 14:32:07

That's perfect. Thanks to you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources