Getting Started with ASP.NET MVC - Part 5: How to do programming with razor syntax
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Home | Ask Question

Loading

Ask in the public forum

Ask your questions in a public forum

My Blog Posts

Recent Blog Posts

  • Strange behaviour of Case statement with char column

    Jan 27 2012 10:11AM | Report Abuse This post is not formatted correctly

    I was trying to do the following: 

    SELECT
CASE  
            WHEN CHARINDEX ('(',sohCusref ) > 0 THEN SUBSTRING(sohCusref ,1,CHARINDEX ('(',sohCusref ) - 1)
            WHEN CHARINDEX (' ',sohCusref ) > 0 THEN SUBSTRING(sohCusref ,1,CHARINDEX (' ',sohCusref ) - 1)
            WHEN CHARINDEX ('/',sohCusref ) > 0 THEN SUBSTRING(sohCusref ,1,CHARINDEX ('/',sohCusref ) - 1)
            ELSE sohCusref
END   as sohCusrefFormatted
FROM dbo.BaseData

    But for some reason it would apply the first condition... after scratching my head for a while I tried changing the operators to '<' ,'<>', '=' and things started working... not applying the correct conditions but was picking up all conditions.

    Colleague suggested i try it with a simple example so tried following:

    SELECT
CASE  
            WHEN CHARINDEX ('/',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('/',sohCusref ) - 1)
            WHEN CHARINDEX (' ',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX (' ',sohCusref ) - 1)
            WHEN CHARINDEX ('(',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('(',sohCusref ) - 1)
            ELSE sohCusref
      END   as sohCusref_formatted
FROM
(
      SELECT 'AAA/BBB)' AS sohCusref UNION ALL
      SELECT 'CCC DDD' AS sohCusref UNION ALL
      SELECT 'EEE(FFF)' AS sohCusref UNION ALL
      SELECT 'GGG/HHH III(JJJ)' AS sohCusref
) test

    Which worked fine... and hence led me to check whether it was something to do with the type which was char(25)... i changed it to varchar and everything started working fine.

    Can anybody highlight the reason why it behaved in this way with a char column?

    Thanks!

    SQL Case Statement CHAR
    Yunus Simjee
    178 · 1% · 201

3  Replies  

Subscribe to Notifications

  • Hi Yunus Simjee,
    as the behavior of char() datatype it pad blank space if your value size is not as enough.

     SELECT  'CHAR(25)' AS [DataType] ,
        CHARINDEX(' ', CAST('EEE(FFF)' AS CHAR(25))) AS [CharIndex] ,
        DATALENGTH(CAST('EEE(FFF)' AS CHAR(25))) AS [DataLength]
GO
SELECT  'VARCHAR(25)' AS [DataType] ,
        CHARINDEX(' ', CAST('EEE(FFF)' AS VARCHAR(25))) AS [CharIndex] ,
        DATALENGTH(CAST('EEE(FFF)' AS VARCHAR(25))) AS [DataLength][DataLength]

    so if you want the same result using char(25) then you have to change your when clause condition.Put ' ' condition comparision at the end of case statement like below example

    --Using VarChar
SELECT
CASE  
            WHEN CHARINDEX ('/',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('/',sohCusref ) - 1)
            WHEN CHARINDEX (' ',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX (' ',sohCusref ) - 1)
            WHEN CHARINDEX ('(',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('(',sohCusref ) - 1)
            ELSE sohCusref
      END   as sohCusref_formatted
FROM
(
      SELECT 'AAA/BBB)' AS sohCusref UNION ALL
      SELECT 'CCC DDD' AS sohCusref UNION ALL
      SELECT 'EEE(FFF)' AS sohCusref UNION ALL
      SELECT 'GGG/HHH III(JJJ)' AS sohCusref
) test

GO
--Using Char(25)
SELECT
CASE  
            WHEN CHARINDEX ('/',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('/',sohCusref ) - 1)
            WHEN CHARINDEX ('(',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX ('(',sohCusref ) - 1)
            WHEN CHARINDEX (' ',sohCusref ) > 0 THEN SUBSTRING(sohCusref,1,CHARINDEX (' ',sohCusref ) - 1)
            ELSE sohCusref
      END   as sohCusref_formatted
FROM
(
      SELECT CAST('AAA/BBB)' AS CHAR(25)) AS sohCusref UNION ALL
      SELECT CAST('CCC DDD'  AS CHAR(25)) AS sohCusref UNION ALL
      SELECT CAST('EEE(FFF)' AS CHAR(25)) AS sohCusref UNION ALL
      SELECT CAST('GGG/HHH III(JJJ)'  AS CHAR(25)) AS sohCusref
) test
    Replied on Jan 28 2012 7:41AM  .  Report Abuse This post is not formatted correctly
    Mitesh Modi
    87 · 2% · 560

Your Reply
Sign Up or Login to post an answer.

Managed Windows Shared Hosting by OrcsWeb

Copyright © Beyondrelational.com