Case query in sqlserver [Resolved]

Posted by Naseer under Sql Server on 4/7/2015 | Points: 10 | Views : 313 | Status : [Member] | Replies : 1
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




Responses

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

Up
0
Down

Resolved
--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

Login to post response