SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Get all characters left of string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fralo
Posting Yak Master

149 Posts

Posted - 03/19/2013 :  14:10:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/19/2013 :  14:22:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 03/19/2013 :  14:29:27  Show Profile  Reply with Quote

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


Be One with the Optimizer
TG
Go to Top of Page

fralo
Posting Yak Master

149 Posts

Posted - 03/19/2013 :  14:32:07  Show Profile  Reply with Quote
That's perfect. Thanks to you both.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000