T-SQL Query - Trim

Create TABLE #Ram (ID int, Stat Varchar(30), Lbl varchar(500))

INSERT INTO #Ram(ID,Stat, Lbl)
VALUES (1, 'null','Rahul,Southern'),
            (2,'UNKNOWN','Ram,Rishi,Tapas'),
            (3, 'UNK', 'Dhoni'),
            (4, ' ', 'Ilayaraja')


SELECT ID,
            case when Stat = null then 'UNKNOWN'
                  WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
                  WHEN Stat = 'UNK' THEN 'UNKNOWN'
                  WHEN Stat = ' ' THEN 'UNKNOWN'
                  ELSE 'UNKNOWN'
                  END,
            RTRIM(Lbl ',')
            From #Ram

The Expected output

1      UNKNOWN            Rahul
2      UNKNOWN            Ram
3      UNKNOWN            Dhoni
4      UNKNOWN            Ilayaraja

Kindly help me to write a query.
Ravee123Asked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
you were almost there:

with ram as (
      select 1 id, 'null' stat,'Rahul,Southern' lbl
union all select 2,'UNKNOWN','Ram,Rishi,Tapas'
union all select 3, 'UNK', 'Dhoni'
union all select 4, ' ', 'Ilayaraja'
)
SELECT id,
            case when Stat is null then 'UNKNOWN'
                  WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
                  WHEN Stat = 'UNK' THEN 'UNKNOWN'
                  WHEN Stat = ' ' THEN 'UNKNOWN'
                  ELSE 'UNKNOWN'
                  END stat,
                  case when CHARINDEX(',',lbl)>0 then left(Lbl, CHARINDEX(',',lbl)-1) else lbl end lbl
 From ram

id	stat	lbl
1	UNKNOWN	Rahul
2	UNKNOWN	Ram
3	UNKNOWN	Dhoni
4	UNKNOWN	Ilayaraja

Open in new window

0
 
LIONKINGCommented:
I think this is a duplicate but here it is:

DECLARE @Ram TABLE (ID int, Stat Varchar(30), Lbl varchar(500))

INSERT INTO @Ram(ID,Stat, Lbl)
VALUES (1, 'null','Rahul,Southern'),
            (2,'UNKNOWN','Ram,Rishi,Tapas'),
            (3, 'UNK', 'Dhoni'),
            (4, ' ', 'Ilayaraja')


SELECT ID,
            case when Stat = null then 'UNKNOWN'
                  WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
                  WHEN Stat = 'UNK' THEN 'UNKNOWN'
                  WHEN Stat = ' ' THEN 'UNKNOWN'
                  ELSE 'UNKNOWN'
                  END,
            CASE WHEN CHARINDEX(',',Lbl)=0 THEN Lbl ELSE LEFT(Lbl,CHARINDEX(',',Lbl)-1) END as Lbl
            From @Ram
0
 
Lee SavidgeCommented:
I suspect this isn't what you're after but without an understanding of the problem this query should do what you ask:

select ID,
case when Stat = null then 'UNKNOWN'
                  WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
                  WHEN Stat = 'UNK' THEN 'UNKNOWN'
                  WHEN Stat = ' ' THEN 'UNKNOWN'
                  ELSE 'UNKNOWN'
                  END as unknown,
case
when charindex(',', rtrim(lbl)) = 0 then rtrim(lbl)
else left(lbl, charindex(',', rtrim(lbl)) - 1)
end as lbl
from #Ram
0
 
Anuradha GoliSystems Development / Support SpecialistCommented:
SELECT ID,
            case when Stat = null then 'UNKNOWN'
                  WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
                  WHEN Stat = 'UNK' THEN 'UNKNOWN'
                  WHEN Stat = ' ' THEN 'UNKNOWN'
                  ELSE 'UNKNOWN'
                  END,    
                  CASE WHEN CHARINDEX( ',',LBL) = 0 then LBL
                  else    SUBSTRING(LBL,0,CHARINDEX( ',',LBL))
                  end
            From #Ram

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.