How to split data and store values using case in sql server

Question

User958664279 posted

Hi i have data like this

Id  Data

1 000000000000000000000000000000000000000000
2 STDSTDSTDSTDSTDSTDSTDSTD
3 000000000000
4 000000000000000000000000000000000000000000000000XXX000000000000000000000000000000000000000000000000000000XXX
5 000XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX000000000000
6 000000000000000000000000XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX000000000000000000000000000000000000000

i want to split data 

like 000 set value for 1

STD set value for 2 

and then max value from this

Answer 1

User603616845 posted

Hi,

I am able to get your 2 points and I assume that you are asking for getting Id and data (only first 3 character). So, you can use like this

select Id, substring(data,0,4) as data from testtable

I didn't get your last point.."and then max value from this".

Hope this will help you.

Thanks

Answer 2

User61956409 posted

Hi nandankesh,

According to your requirement, you could refer to the following SQL query.

select * from TbDATA

SELECT [Id],
CASE 
      WHEN [Data] like '%000%' THEN REPLACE([Data], '000', '1') 
      WHEN [Data] like '%STD%' THEN REPLACE([Data], 'STD', '2') 
   END 
   AS [Data] 
FROM TbDATA

DROP TABLE MYTB

SELECT 
CASE 
      WHEN [Data] like '%000%' THEN REPLACE([Data], '000', '1') 
      WHEN [Data] like '%STD%' THEN REPLACE([Data], 'STD', '2') 
   END 
   AS [Data] 
   INTO MYTB
   FROM TbDATA

SELECT MAX([Data])
FROM MYTB 

Result:

Best Regards,

Fei Han