Post reply

Problem with the code

  • July 20, 2011 at 8:07 am

    #145912

    For some reason the code is not working properly, can someone assist me:

    Here is the code:

    case

    when col005 = '' THEN 0

    when patindex('%.%', col005) > 0 then

    ltrim(substring(isnull(replace(replace(replace(col005,'$',''),'-',''),',',''),0),1,

    len(isnull(replace(replace(replace(col005,'$',''),'-',''),',',''),0))-3)) else

    ltrim(isnull(replace(replace(replace(col005,'$',''),'-',''),',',''),0))

    end as '2012'

    Here is the sample of the data

    column1 column2

    51199511996

    988298827

    124912496

    Column1 data in the code,column2 data in the database,what is wrong with this code

    Thank you

  • July 20, 2011 at 8:17 am

    #1355655

    Krasavita

    Please check the article linked in my signature to get better responses from user on this site.

    Please define "Not Working Properly"

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • July 20, 2011 at 9:02 am

    #1355723

    Krasavita (7/20/2011)

    For some reason the code is not working properly, can someone assist me...Thank you

    Ensure that each result expression evaluates to the same datatype:

    DECLARE @col005 VARCHAR(15) = 'SOME$VA.LUE-'

    SELECT CASE

    WHEN @col005 = '' THEN '0'

    WHEN patindex('%.%', @col005) > 0 THEN

    ltrim(substring(isnull(replace(replace(replace(@col005,'$',''),'-',''),',',''),0),1,

    len(isnull(replace(replace(replace(@col005,'$',''),'-',''),',',''),0))-3))

    ELSE ltrim(isnull(replace(replace(replace(@col005,'$',''),'-',''),',',''),0))

    END AS '2012'

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 20, 2011 at 12:49 pm

    #1355958

    Thank you very much, The field is varchar 255.What do I need to do to fix it?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply