case when - 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 when

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-26 : 00:40:31

how can i set if the data has 10 digits and starts with A, sample: Axxxxxxxxx name as 10digits.
if is 5 digits sample: xxxxx name as 5digits.

can i do that in case when condition?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-26 : 00:57:03

Can you elaborate the requirement?

--
Chandu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-09-26 : 02:13:41

i have data as below:

tag
A23456789
A98765432
12345
23456
9000
890

if those 10 digits and starts with A, name new column as 10digits
if is 5 digits, name new column as 5digits
others as Other.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-26 : 02:46:20

[code]DECLARE @Sample TABLE
(
Tag VARCHAR(100) NOT NULL
);

INSERT @Sample
(
Tag
)
VALUES ('A23456789'),
('A98765432'),
('A9876543210'),
('A987654321'),
('12345'),
('23456'),
('9000'),
('890');

-- SwePeso
WITH cteTags(Tag, Class)
AS (
SELECT Tag,
CASE
WHEN Tag LIKE 'A[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 10
WHEN Tag LIKE '[0-9][0-9][0-9][0-9][0-9]' THEN 5
ELSE 0
END AS Class
FROM @Sample
)
SELECT CASE
WHEN Class = 10 THEN Tag
ELSE ''
END AS [10 digits and starts with A],
CASE
WHEN Class = 5 THEN Tag
ELSE ''
END AS [5 digits],
CASE
WHEN Class = 0 THEN Tag
ELSE ''
END AS Other
FROM cteTags;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-26 : 07:44:41

In Peso's code, change

WHEN Tag LIKE 'A[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 10

into

WHEN Tag LIKE 'A[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 10

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-26 : 08:32:24

Is letter "A" a digit?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-26 : 12:55:10

I think OP meant the length of 10 (A followed by 9 digits)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources