SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case when
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 09/26/2013 :  00:40:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/26/2013 :  00:57:03  Show Profile  Reply with Quote
Can you elaborate the requirement?


--
Chandu
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 09/26/2013 :  02:13:41  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/26/2013 :  02:46:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/26/2013 :  07:44:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/26/2013 :  08:32:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is letter "A" a digit?



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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/26/2013 :  12:55:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think OP meant the length of 10 (A followed by 9 digits)

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 09/26/2013 12:55:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000