Converting Parts of varchar to Integer

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 2005 Forums

Transact-SQL (2005)

Converting Parts of varchar to Integer

Author

Topic

excelthoughts
Starting Member

5 Posts

Posted - 2007-03-27 : 22:17:08

Can someone tell me why this fails? It should just return 'xyz' in the first part of the CASE statement.

The goal is to ORDER BY CaseNo like this: 100, 100.1, 100.2,100.3 ... 100.12, xyz

DECLARE @CaseNo VARCHAR(10)
SET @CaseNo='xyz'

SELECT CASE WHEN CHARINDEX('.',@CaseNo)=0 then @CaseNo ELSE Convert (int, parseName(@CaseNo,2)) END as Second,
CASE WHEN CHARINDEX('.',@CaseNo)=0 then @CaseNo ELSE Convert (int, parseName(@CaseNo,1)) END as FIRST

regards
excelthoughts

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 11:10:23

All of the parts of a CASE must return the same data type - otherwise what will the type of the column be?

So you cannot have one THEN return a varchar and another THEN return an int. You could rewrite it like this (note - I used cast because it is ANSI standard, you could do it with convert too).

DECLARE @CaseNo VARCHAR(10)
SET @CaseNo='xyz'

SELECT CASE WHEN CHARINDEX('.',@CaseNo)=0 THEN @CaseNo 
    ELSE cast(cast(parseName(@CaseNo,2) as int) as varchar(10)) END as Second,
CASE WHEN CHARINDEX('.',@CaseNo)=0 THEN @CaseNo 
    ELSE cast(cast(parseName(@CaseNo,1) as int) as varchar(10)) END as FIRST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources