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

Posted on 2009-04-27
Medium Priority
Last Modified: 2012-05-06
I want to evaluate fd.id 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,fd.id) < 10 then  'f000'+ convert(varchar(4),fd.id )
	      when convert (int,fd.id) < 100 then 'f00'+ convert(varchar(4),fd.id )
	      when convert (int, fd.id)< 1000 then 'f0'+ convert(varchar(4),fd.id )
	      else fd.id

Open in new window

Question by:dwiseman3
LVL 17

Accepted Solution

k_murli_krishna earned 1050 total points
ID: 24243933
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

BrandonGalderisi earned 450 total points
ID: 24244028
You can't have the output of a case statement return two different data types.  If fd.id is int, then in your else it must be cast as the same datatype as the rest of your case


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

Open in new window


Author Closing Comment

ID: 31575056
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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