View With CASE - Need help

[coboldave]

I need to parse a column into two columns filtering out the first 4 characters and converting the fraction into a decimal and the others that apply e.g. 10GA to decimal also. In the second column I need to parse from 6 to the next space.

This is what I have so far. I have the fraction conversion worked out, but if there is e.g. 1" it does not work. I also need to come back and convert a value of 10 GA where on the first run it was 10GA.

Description
1/4 GR80 16.75" X 17.15"
3/16 HR 96'' x 261''
1/2 HR 17.00'' x 20.00''
5/8 HR 12.00'' x 22.375''
3/8 HR 2.25" X 5.05"
1/2 GR50 10.13'' x 12.00'
10GA HR 25.13" X 82.97",0.135
10 GA HR 37.44" X 79.19"
1" HR 8.25" x 22.75"

SELECT description,CASE Left(cast(description as varchar(4)), 30)
WHEN '1/8' then '0.125'
WHEN '3/16' then '0.187'
WHEN '5/16' then '0.313'
WHEN '10GA' then '0.135'
WHEN '10 GA' then '0.135'
WHEN '1"' then '1.000'

END as 'tcode'
FROM jobasmbl

All suggestions and help welcomed and appreciated.

 

[GeertVerhoeven]

Hi,

The structure of a CASE statement is different then the one you are trying to use.

The real structure is:
CASE WHEN <expression>
THEN <statement>
[ ELSE <statement> ]
END

Your query will change into:
SELECT description,CASE
WHEN Left(cast(description as varchar(4)), 30) = '1/8' then '0.125'
WHEN Left(cast(description as varchar(4)), 30) = '3/16' then '0.187'
WHEN Left(cast(description as varchar(4)), 30) = '5/16' then '0.313'
WHEN Left(cast(description as varchar(4)), 30) = '10GA' then '0.135'
WHEN Left(cast(description as varchar(4)), 30) = '10 GA' then '0.135'
WHEN Left(cast(description as varchar(4)), 30) = '1"' then '1.000'
END as 'tcode'
FROM jobasmbl

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[bborissov]

Geert, the case construction used by coboldave is perfectly valid.

Coboldave, try this:

DECLARE @temp table (description varchar(200))
INSERT INTO @Temp VALUES ('1/4 GR80  16.75" X 17.15"')
INSERT INTO @Temp VALUES ('3/16 HR 96'' x 261''')
INSERT INTO @Temp VALUES ('1/2 HR 17.00'' x 20.00''')
INSERT INTO @Temp VALUES ('5/8 HR 12.00'' x 22.375''')
INSERT INTO @Temp VALUES ('3/8 HR  2.25" X 5.05"')
INSERT INTO @Temp VALUES ('1/2 GR50  10.13'' x 12.00''')
INSERT INTO @Temp VALUES ('10GA HR  25.13" X 82.97",0.135')
INSERT INTO @Temp VALUES ('10 GA HR  37.44" X 79.19"')
INSERT INTO @Temp VALUES ('1" HR  8.25" x 22.75"')

SELECT description, CASE Left(description, CHARINDEX(' ',description)-1)
       WHEN '1/8'   then '0.125'
       WHEN '3/16'  then '0.187'
       WHEN '5/16'  then '0.313'
       WHEN '10'    then '0.135'
       WHEN '10GA'  then '0.135'
       WHEN '1"'    then '1.000'
       ELSE ''
END as 'tcode'
FROM @temp

If this is what you want just remove table declaration and change FROM @TEMP to your actual table.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[GeertVerhoeven]

Ok, didn't knew that.



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[SQLScholar]

Hi,

I may be on the completely wrong track - but it seems to me you converting it to 4 varchar, then doing left 30. So the length of the string (inc spaces) is 4. So it would then have to be.....

WHEN '1/8 ' then '0.125'

For instance? I may be completely wrong as i say but i thought i would give it a go!

Dan



----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[bborissov]

Plank,
when you convert to varchar all trailing spaces are removed.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLScholar]

Ah - ok, i will go away. I thought it left them.



Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------