[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Casting varchar as int

Posted on 2008-11-03
14
Medium Priority
?
607 Views
Last Modified: 2011-10-19
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

0
Comment
Question by:MiracleByDesign
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22872991
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
 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 2000 total points
ID: 22873410
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
 

Author Comment

by:MiracleByDesign
ID: 22878351
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22878768
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
 

Author Comment

by:MiracleByDesign
ID: 22878859
'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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22878941
Please show your query and if you know the column which has data like above, then please share.
0
 

Author Comment

by:MiracleByDesign
ID: 22879299
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882048
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
 

Author Comment

by:MiracleByDesign
ID: 22882227
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882341
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22882377
I want to help, but something in my understanding of your problem is not correct. Check out my screen shot below.
results.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22882388
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
 

Author Comment

by:MiracleByDesign
ID: 22889914
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
 

Author Closing Comment

by:MiracleByDesign
ID: 31512952
The issue was with collation in the database.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

829 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