Parsing troubles

[phbrady]

When running the query "SELECT formulaid FROM tblFormula"
I get the results:
AB333
AB333CX1
CA333
CA333CX3

I need to simply parse the last 3 char's off the end of the fields containing "CX" and leave the rest as they are. I know I can use the CHARINDEX function to do this but can't figure out exactly how?? Does SQL Server have an equivelant to MS Access's IIF() statement?
Help greatly appreciated!

 

[SQLBill]

Will you ALWAYS have the same amount of characters before the CX??? If so use SUBSTRING:

SELECT SUBSTRING(formulaid,1,5)
FROM tblFormula

Most people switching from Access to SQL Server replace IIF() with SQL Server's CASE.

-SQLBill

 

[tlbroadbent]

Here is one possible solution using the CASE function which is the roughly equivalent to IIF in VB/Access.

Select
Case
When charindex('cx',formulaid)=0 Then formulaid
Else Left(formulaid,len(f1)-3)
End
FROM tblFormula

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[phbrady]

Case statement works beautifully.

Thanks!