?
Solved

T-SQL Query - Trim

Posted on 2012-09-05
4
Medium Priority
?
616 Views
Last Modified: 2012-09-17
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.
0
Comment
Question by:Ravee123
4 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38368518
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38368521
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
 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 38368545
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
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 38368574
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question