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?
 
HoggZillaConnect With a Mentor Commented:
I want to help, but something in my understanding of your problem is not correct. Check out my screen shot below.
results.bmp
0
 
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
 
HoggZillaConnect With a Mentor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
HoggZillaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.