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

ahmed elbarbary

6y732

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishvjit Shinde6y ago
Accepted answer
1. SELECT partid,
2. PartDone,
3. CASE WHEN SUBSTRING(PartDone, 1, 1) = 0 THEN 'no' ELSE
4. CASE WHEN SUBSTRING(PartDone, 1, 1) = 1 THEN 'yes'ELSE
5. CASE WHEN SUBSTRING(PartDone, 1, 1) = 2 THEN 'null' else
6. 'null'end end end as [first],
7. CASE WHEN SUBSTRING(PartDone, 2, 1) = 0 THEN 'no' ELSE
8. CASE WHEN SUBSTRING(PartDone, 2, 1) = 1 THEN 'yes'ELSE
9. CASE WHEN SUBSTRING(PartDone, 2, 1) = 2 THEN 'null' else
10. 'null'end end end as [second],
11. CASE WHEN SUBSTRING(PartDone, 3, 1) = 0 THEN 'no' ELSE
12. CASE WHEN SUBSTRING(PartDone, 3, 1) = 1 THEN 'yes'ELSE
13. CASE WHEN SUBSTRING(PartDone, 3, 1) = 2 THEN 'null' else
14. 'null'end end end as [third],
15. CASE WHEN SUBSTRING(PartDone, 4, 1) = 0 THEN 'no' ELSE
16. CASE WHEN SUBSTRING(PartDone, 4, 1) = 1 THEN 'yes'ELSE
17. CASE WHEN SUBSTRING(PartDone, 4, 1) = 2 THEN 'null' else
18. 'null'end end end as [four]
19. FROM part;

Jignesh Kumar6y ago

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 Krishnan6y ago

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