Case query in sqlserver [Resolved]

Posted by Naseer under Sql Server on 4/7/2015 | Points: 10 | Views : 666 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

could you please help me to get the below expected outputs:

If

FixBy='QR9-02-Feb' o/p:902

FixBy='QR10-04-Apr' o/p:1004

FixBy='TextData' o/p:100

SELECT (CASE WHEN isnumeric(substring([FixBy], 3, 1)) = 1 THEN substring([FixBy], 3, 1) ELSE '1' END) +
(CASE WHEN isnumeric(substring([FixBy], 5, 2)) = 1 THEN substring([FixBy], 5, 2) ELSE '00' END) AS sprint,

For FixBy='QR9-02-Feb' and FixBy='TextData' above query is working as expected by facing issue at FixBy='QR10-04-Apr'
while passing FixBy='QR10-04-Apr' its returning as 1-0

Thanks in advance.
Naseer

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 4/8/2015 [Member] [MVP] Platinum | Points: 50

0	--Sample Data Declare @tab table (FixBy varchar(100)) insert @tab SELECT 'QR9-02-Feb' union all -- o/p:902 SELECT 'QR10-04-Apr' union all --o/p:1004 SELECT 'TextData' --o/p:100 --Query Select ISNULL(NULLIF(REPLACE(Left(SubString(FixBy, PatIndex('%[0-9.-]%', FixBy), 8000), PatIndex('%[^0-9.-]%', SubString(FixBy, PatIndex('%[0-9.-]%', FixBy), 8000) + 'X')-1), '-',''), ''), '100') FROM @tab -- @tab is table name Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Naseer, if this helps please login to Mark As Answer. \| Alert Moderator

--Sample Data 
Declare @tab table (FixBy varchar(100))
insert @tab 
SELECT 'QR9-02-Feb' union all -- o/p:902 
SELECT 'QR10-04-Apr' union all --o/p:1004 
SELECT 'TextData' --o/p:100 

--Query 
Select ISNULL(NULLIF(REPLACE(Left(SubString(FixBy, PatIndex('%[0-9.-]%', FixBy), 8000), PatIndex('%[^0-9.-]%', SubString(FixBy, PatIndex('%[0-9.-]%', FixBy), 8000) + 'X')-1), '-',''), ''), '100')
FROM @tab -- @tab is table name

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts