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)
 There has to be a better way (CASE and CAST)

Author  Topic 

Nexzus
Starting Member

14 Posts

Posted - 2013-09-13 : 11:46:42
Hi,

Column col is a VARCHAR with values like 'AA-###' or 'AA-####'

I need to output a different string number range depending on the numbers, 0001-0100, 1501-2000, etc., up to 3001-3500

What I have (and works) is:

CASE 
WHEN CAST( SUBSTRING(col, CHARINDEX('-', col) + 1,
LEN(col)) AS INT) < 100
THEN '0001-0100'
WHEN CAST( SUBSTRING(col, CHARINDEX('-', col) + 1,
LEN(col)) AS INT) > 100
AND
CAST( SUBSTRING(E_INA06, CHARINDEX('-', col) + 1, LEN(col)) AS INT) <= 200
THEN '0101-0200'
WHEN CAST( SUBSTRING(col, CHARINDEX('-', col) + 1, LEN(col)) AS INT) > 200
AND
CAST( SUBSTRING(col, CHARINDEX('-', col) + 1, LEN(col)) AS INT) <= 300
THEN '0201-0300'

etc.

As I have 14 of these ranges, that's a maximum of 27 casts per row.
Creating a column alias before hand didn't work because of some SQL reason.

This pretty much only has to be done once, so this is more of a curiousity/learning thing.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 12:09:49
Precomputing the "CAST( SUBSTRING(col, CHARINDEX('-', col) + 1, LEN(col))" in a subquery or cte would be one way to simplify it.
Is that what you meant when you said you aliased it? If that didn't work, can you post the code that you tried?
Go to Top of Page

Nexzus
Starting Member

14 Posts

Posted - 2013-09-13 : 13:02:07
It was something like:

SELECT CAST( SUBSTRING(col, CHARINDEX('-', col) + 1, 
LEN(col)) AS INT) AS ColAlias,

CASE
WHEN ColAlias < 100 THEN '0001-0100'
END

I got the Invalid ColumnName 'ColAlias' error.

Didn't think of going the SubQuery route. I'll have to give a shot, see if it works.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-13 : 13:14:16
The aliases you define in the select list cannot be used anywhere else other than in the order by clause. So the workaround is to use a cte or subquery - for example like this:
SELECT 
CASE
WHEN ColAlias < 100 THEN '0001-0100' THEN .....
END
FROM (
SELECT CAST( SUBSTRING(col, CHARINDEX('-', col) + 1,
LEN(col)) AS INT) AS ColAlias
FROM yourTable
) AS s
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-13 : 14:41:17
[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Data VARCHAR(7) NOT NULL
);

INSERT @Sample
(
Data
)
SELECT 'AA-' + CAST(1 + ABS(CHECKSUM(NEWID())) % (POWER(10, l) - 1) AS VARCHAR(4))
FROM (
SELECT 3 + ABS(CHECKSUM(NEWID())) % 2 AS l
FROM master.dbo.spt_values
) AS d

-- Display original data
SELECT Data
FROM @Sample

-- Solution by SwePeso
SELECT RIGHT('000' + CAST(1 + 100 * theGroup AS VARCHAR(4)), 4) + '-' + RIGHT('0000' + CAST(100 + 100 * theGroup AS VARCHAR(5)), CASE WHEN theGroup = 99 THEN 5 ELSE 4 END) AS Section,
COUNT(*) AS Items
FROM (
SELECT (SUBSTRING(Data, 4, LEN(Data) - 3) - 1) / 100 AS theGroup
FROM @Sample
) AS d
GROUP BY theGroup;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -