Case Statement using LIKE - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Case Statement using LIKE

Author

Topic

dhorn
Starting Member

4 Posts

Posted - 2010-02-03 : 10:17:48

I need to create a column that essentially checks whether the 2nd to last or last character of another column is alphabetic, and if they are, removes them. I thought this code would work, but it has issues with the LIKE statements. Any ideas?

		SELECT	s.approvedDate, ss.FLOORAREADESCRIPTION + (CASE WHEN ISNULL(ss.BUILDINGDESCRIPTION, '') = '' THEN '' ELSE ' (' + ss.BUILDINGDESCRIPTION + ')' END) AS 'Position',
					ss.EMPLOYEENAME + ' (' + ss.EMPLOYEENO + ')' AS 'Name',
					st.Description AS 'Sample Type',
                    UPPER(ss.SAMPLENO) AS 'Sample No.',
					UPPER(ss.MasterSampleNo) AS 'Master No.',
					CASE ss.SAMPLENO WHEN SUBSTRING(UPPER(ss.SAMPLENO), LEN(ss.SAMPLENO) - 2, 1) LIKE '[A-Z]' THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 2)
									 WHEN SUBSTRING(UPPER(ss.SAMPLENO), LEN(ss.SAMPLENO) - 1, 1) LIKE '[A-Z]' THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1)
									 ELSE RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1) END AS GroupSamp,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:05:07

try


...
CASE ss.SAMPLENO WHEN PATINDEX('%[A-Z]_',UPPER(ss.SAMPLENO)) >0 THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 2)
									 WHEN PATINDEX('%[A-Z]',UPPER(ss.SAMPLENO)) >0 THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1)
									 ELSE RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1) END AS GroupSamp

dhorn
Starting Member

4 Posts

Posted - 2010-02-03 : 12:52:29

I get the following error when I run this code in sql server 2005:

Msg 102, Level 15, State 1, Procedure IH_WeeklySummary_edit, Line 21
Incorrect syntax near '>'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 12:55:00

it should be


..
CASE ss.SAMPLENO WHEN PATINDEX('%[A-Z]_',UPPER(ss.SAMPLENO)) >0 THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 2)
									 WHEN PATINDEX('%[A-Z]',UPPER(ss.SAMPLENO)) >0 THEN RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1)
									 ELSE RIGHT(ss.SAMPLENO, LEN(ss.SAMPLENO) - 1) END AS GroupSamp

dhorn
Starting Member

4 Posts

Posted - 2010-02-03 : 13:00:01

Yes, that works perfectly. Thank you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 13:01:13

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources