Conversion failed when converting the varchar value 'f0728' to data type int

Posted on 2009-04-27
Last Modified: 2012-05-06
I want to evaluate as a numeric value. It is a varchar(10) field. Then I want the output
concatenated with 'f' and zero filled to produce a 5 character field.
If the value is < 10 then output 'f0009'
If the value is >= 10 and < 100 then 'f0099'
If the value is >= 100 and < 1000 then 'f0999'
else f9999
I get the error using this code.
I tried CAST and got the same error.
How should I evaluate the varchar field as a number and then select it as varchar?
a_id =       CASE when convert(int, < 10 then  'f000'+ convert(varchar(4), )

	      when convert (int, < 100 then 'f00'+ convert(varchar(4), )

	      when convert (int,< 1000 then 'f0'+ convert(varchar(4), )


Open in new window

Question by:dwiseman3
    LVL 17

    Accepted Solution

    Just take SUBSTRING of converted 5 character string as:
    SUBSTRING(5charstring,2,4), convert this into INTEGER using CONVERT function or CAST. You CASE logic will be pretty much the same. Just make sure to go for an INTEGER after turning it into one of its genre.
    LVL 39

    Assisted Solution

    You can't have the output of a case statement return two different data types.  If is int, then in your else it must be cast as the same datatype as the rest of your case


    select case when<=1000 then 'f'   right('0000' convert(varchar(10),,4)
        else convert(varchar(10),

    Open in new window


    Author Closing Comment

    the following statement worked:
    when cast(fd.ident as int) <10 then 'f000' + substring(fd.ident,4,4)
    I tried Brandon's but I think the statement has to convert to a number before comparing it to 10,100 or 1000.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now