Casting varchar as int

Hi Experts!

I have to create a search query for various fields.  The search string is type varchar and all of the fields in a table are varchars of different lengths.  The problem is two fields FEIN and ERN store mostly numeric data.  I cannot change the data types and I have tried to cast the varchar as an int in my where clause but I get an error.   An example of a FEIN could either be 11-1111111 or 111111111.  ERN are always 7 digits. Can anyone assist me?
Thanks
Miracle by Design
WHERE ((Cast(FEIN as int)) = cast(@Criteria as int) and a.JTAppID is not null)

Open in new window

MiracleByDesignAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Just replace the '-' first then CAST to INT.  Since it is 9 characters, INT should be fine but can move up to BIGINT if you run into bigger numbers.

CAST(REPLACE(@Criteria, '-', '') AS INT)

Hopefully that helps.
0
Steve HoggITCommented:
Here is a pretty cool little function that cleans a string and keeps only numbers. I stole it, don't tell anyone. :-)

create function dbo.NumberClean
(@String_in nvarchar(100))
returns bigint
as
begin
declare @NumberStr nvarchar(100), @Number_out bigint
 
;with Chars (Seq, Chr) as
        (select number, substring(@string_in, number, 1)
        from dbo.Numbers
        where number between 0 and len(@string_in))
 
        select @numberstr = coalesce(@numberstr + chr, chr)
        from chars
        where chr like '[0-9]'
 
        select @number_out = @numberstr
 
return @number_out
end

Open in new window

0
MiracleByDesignAuthor Commented:
When I try to cast the varchar as an int.  I get the following error message: Syntax error converting the varchar value '73168x777' to a column of data type int
Here is the syntax I used:  WHERE (Cast(FEIN as int) = CAST(REPLACE(@Criteria, '-', '') AS INT)and a.JTAppID is not null).

I am going to try the function now.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
You can't cast '73168x777' to an int before stripping out the x.  So you will have to come up with a routine as suggested previously to strip out all non-numeric characters.
0
MiracleByDesignAuthor Commented:
'73168x777' was not the number I was casting which is why the error does not make sense.  My test for FEIN is 86317922.  So it should work but does not.
0
Kevin CrossChief Technology OfficerCommented:
Please show your query and if you know the column which has data like above, then please share.
0
MiracleByDesignAuthor Commented:
Here is the code.  @Criteria = 86317922
Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
			
			    Where (Cast(FEIN as int) = CAST(REPLACE(@Criteria, '-', '') AS INT)and a.JTAppID is not null)
				
			Union all
 
				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 
				LegalName, DbaName, Address, 
				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 
				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
				Join Assignment a on a.JtAppID = es.JtAppID 
				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
				Left Join AdminUsers u on u.UserID = a.UserID 
			
				WHERE (Cast(FEIN as int) = CAST(REPLACE(@Criteria, '-', '') AS INT)and a.JTAppID is not null)

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Do you know which column has this data: '73168x777'?  Just looking at your table aside from this query...

I would suggest changing this code;
Where (Cast(FEIN as int) = CAST(REPLACE(@Criteria, '-', '') AS INT)

to this:
Where (Cast(REPLACE(REPLACE(FEIN,'-', ''),'x','')  as int) = @Criteria

If you have more than these two variations, then you will need to write a clean routine as suggested.
0
MiracleByDesignAuthor Commented:
73168x777 does not exist in the table.  The FEIN field is a varchar(9) and the data is 86317922.  The data 73168x777 only appeared on the SQL error message after the query ran.  In the table the FEIN field has no characters currently so I do not know why I am getting the errors.  Both example does not work.  Any other ideas would be appreciated.  I do not know what you mean by a "clean routine" this is a simple query with a Union in it.  All I need to do is match the type in FEIN in the where clause to a field that is type varchar(9) in the database. Which for some reason is not working.  Since I have assigned 500 to this question, I was hoping for a few more type examples.  I need this fixed by end of business on Wednesday if possible.  Thanks in advance!
0
Kevin CrossChief Technology OfficerCommented:
If you are not seeing that value or any odd characters in the data in any of the columns involved in the query in either of the queries involved in the UNION then a clean routine (i.e. a function that removes unwanted characters from string in order to cast it to an INT -- I have given you examples of replacing 'x' and '-' -- this function would be along the same lines but for all non-numeric values) won't do any good.

I have not seen SQL server give such a descriptive message pointing to some data not being able to be casted and the data not exist; therefore, I am not sure what to suggest next.  Anything is possible, so not saying there is not a solution; I just don't know it and wanted to let you know so you can seek other experts to answer.

Best regards,
Kevin
0
Steve HoggITCommented:
I want to help, but something in my understanding of your problem is not correct. Check out my screen shot below.
results.bmp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve HoggITCommented:
Write the following select and post here. Also, what is the value of @Criteria you are using in your sql?
Select Distinct FEIN
FROM ERS_Search

Open in new window

0
MiracleByDesignAuthor Commented:
HoggZilla....I am sure your solution work and I will give you the points for trying.  It appears there was a collation issue with the database.
0
MiracleByDesignAuthor Commented:
The issue was with collation in the database.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.