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
Go to Top of Page

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
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2013-03-19 : 14:32:07
That's perfect. Thanks to you both.
Go to Top of Page
   

- Advertisement -