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.
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:tagA23456789A9876543212345234569000890if those 10 digits and starts with A, name new column as 10digitsif is 5 digits, name new column as 5digitsothers 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');-- SwePesoWITH 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 OtherFROM 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, changeWHEN Tag LIKE 'A[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 10intoWHEN Tag LIKE 'A[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN 10MadhivananFailing 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)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|