There has to be a better way (CASE and CAST)

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?

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.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources