How to separate field partdone to text yes or no or null bas

Question

How to separate field partdone to text yes or no or null based on position ?

I work on SQL server 2012 and have table parts have two field

partId int
PartDone nvarchar(50)
partId partDone
555444 0012
911877 0221

the length of partDone field is 4 numbers

I need to loop within partDone field

if 0 then no

if 1 then yes

if 2 then null

FinalResult

partId partDone first second third four
555444 0012 no no yes null
911877 0221 no null null yes

this field partDone accept only 0 or 1 or 2

and I need to divide them to yes or no or null based on position of every number

What I have tried:

select partId,partDone from Parts

Vishvjit Shinde · Answer

SELECT partid, PartDone, CASE WHEN SUBSTRING (PartDone, 1, 1) = 0 THEN 'no' ELSE CASE WHEN SUBSTRING (PartDone, 1, 1) = 1 THEN 'yes' ELSE CASE WHEN SUBSTRING (PartDone, 1, 1) = 2 THEN 'null' else 'null' end end end as [ first ], CASE WHEN SUBSTRING (PartDone, 2, 1) = 0 THEN 'no' ELSE CASE WHEN SUBSTRING (PartDone, 2, 1) = 1 THEN 'yes' ELSE CASE WHEN SUBSTRING (PartDone, 2, 1) = 2 THEN 'null' else 'null' end end end as [ second ], CASE WHEN SUBSTRING (PartDone, 3, 1) = 0 THEN 'no' ELSE CASE WHEN SUBSTRING (PartDone, 3, 1) = 1 THEN 'yes' ELSE CASE WHEN SUBSTRING (PartDone, 3, 1) = 2 THEN 'null' else 'null' end end end as [third], CASE WHEN SUBSTRING (PartDone, 4, 1) = 0 THEN 'no' ELSE CASE WHEN SUBSTRING (PartDone, 4, 1) = 1 THEN 'yes' ELSE CASE WHEN SUBSTRING (PartDone, 4, 1) = 2 THEN 'null' else 'null' end end end as [four] FROM part;

Jignesh Kumar · Answer

Hi Ahmed, Please try below code CREATE table PartTable ( partId int , PartDone nvarchar(50) ) INSERT INTO PartTable VALUES (555444, '0012' ) INSERT INTO PartTable VALUES (911877, '0221' ) select partId, CASE ( SELECT SUBSTRING (PartDone,1, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END as First , CASE ( SELECT SUBSTRING (PartDone,2, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END as Second , CASE ( SELECT SUBSTRING (PartDone,3, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END as Third , CASE ( SELECT SUBSTRING (PartDone,4, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL end as Four FROM PartTable

Navaneeth Krishnan · Answer

CREATE table #table1 ( partId int, PartDone nvarchar(50), firstValue NVARCHAR(10), secondValue NVARCHAR(10), thirdValue NVARCHAR(10), fourthValue NVARCHAR(10), ) INSERT INTO #table1 VALUES(555444, '0012', NULL, NULL, NULL, NULL) INSERT INTO #table1 VALUES(911877, '0221', NULL, NULL, NULL, NULL) UPDATE T SET firstValue = CASE (SELECT SUBSTRING(T.PartDone,1, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END ,secondValue = CASE (SELECT SUBSTRING(T.PartDone,2, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END , thirdValue = CASE (SELECT SUBSTRING(T.PartDone,3, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL END , fourthValue = CASE (SELECT SUBSTRING(T.PartDone,4, 1)) WHEN 0 THEN 'no' WHEN 1 THEN 'yes' WHEN 2 THEN NULL end FROM #table1 AS T SELECT * FROM #table1 DROP TABLE #table1