SQL Server insert query - Checking if a sql server value is an integer before inserting into a table. If it is not, then replacing it with 00000000

When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column) 

I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000'

ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer.

Does anyone have any ideas?

2/6/2009 4:55:44 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies

Similar Articles

[PageSpeed] 4

Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-aware

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
2/6/2009 12:35:42 PM


For that Purpose there is isnumeric

U can write

Select isnumeric(urvalue)

If that output comes 1 then it is number other wise not


Try this example


declare @number nvarchar(20)

Set @number='20'

Select isnumeric(@number)




MVP ASP/ASP.Net Read My Blog


"Success doesn't come to you…you go to it."--Marva Collins

"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous

2/6/2009 12:57:47 PM

Thanks, yes its useful.

I also found this:


I'm almost there. I use

CASE WHEN isnumeric(EmployeeNumber) = 1 THEN EmployeeNumber ELSE '00000000' END AS EmployeeNumber

which works for the "isnumeric" pre-defined function above but I want to use the "isReallyInteger" user defined function below in my stored procedure;

how do I use a user defined function ( below) in my stored procedure. I have it in my database now but don't know how to refer/use it.

CREATE FUNCTION dbo.isReallyInteger  
    @num VARCHAR(64)  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  
    WHEN PATINDEX('%[^0-9-]%', @num) = 0  
        AND CHARINDEX('-', @num) <= 1  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 

2/6/2009 11:24:01 PM


CASE WHEN dbo.IsReallyInteger(EmployeeNumber) = 1 THEN EmployeeNumber ELSE '00000000' END AS EmployeeNumber


But... that function isn't really necessary.  With a little tricky manipulation, you can use the IsNumeric function to validate an integer.  Like this...



CASE WHEN IsNumeric(EmployeeNumber + '.0e0') = 1 THEN EmployeeNumber ELSE '00000000' END AS EmployeeNumber

Or... if you want to only allow positive integers....


CASE WHEN IsNumeric('-' + EmployeeNumber + '.0e0') = 1 THEN EmployeeNumber ELSE '00000000' END AS EmployeeNumber


You see.... if your EmployeeNumber is negative, and you add another negative sign in front, the result will not be numeric (isnumeric returns 0).  Similarly, if your Employee number already has a decimal point or scientific notation, then adding another decimal point (or scientific notation) will cause the IsNumeric function to return 0.

Make sense?

2/6/2009 11:34:46 PM

Very clever George.

Simple solution but a very good one.


2/7/2009 4:37:06 PM

Similar Artilces:

Variable Insert to SQL server insert satement setting values for the @variable INSIDE sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html ...

how do i insert data into sql server using data from textboxes and sql connection and sql command
anyone can give me a simple explaination and code.. Thanx in advance Take a look @ Sample Code: How to add...Sushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb There are a lot of tutorials out there, but I know they can be a bit confusing as they all approach things slightly differently. Basically a nice simple version would go like this:' *** First set up your connectionDim conConnection As New SqlConnection("server=servername;database=yourdatabase;uid=yourname;pwd=yourpassword;")' *** This line takes care of your sommand text and command connection in one ...

SQL Server 2005 Installation problems with SQL Server Express & SQL Server 2000
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well.  (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed.  I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number o...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

SQL Server 2000 and SQL Server 2005 in the same server
Can enyone please send me a link to a white paper explaining how to run both SQL Server 2000 and 2005 in the same machine?Thanks. I don't know about the white paper but I had both running on my machine for a while by making the SQL 2000 a named instance. So for SQL 2005 connection strings my server name was (local) while for SQL 200 using the named instance it was (local)\joesql where joesql was the instance nameHope it helps,Joe http://www.joeaudette.comhttp://www.mojoportal.com...

Moving Tables/Procedures from SQL Server Express to SQL Server
My searches have come up blank, so I'm hoping someone else can point me in the right direction. I'm done some development in SQL Server Express, working on tables and procedures.  I've now got to move those tables/procedures into SQL Server and I don't know of an easy way to do this.  Are there any tools I can take advantage of that would allow me to get all these objects moved?  The only way I can see to do it is to move objects over one-by-one.  This would be really time-consuming, and I'd like to avoid it if possible. I'm positive I'm...

How to copy a table from SQL Server 1 to SQL Server 2
I have 2 SQL Server. I need copy a table from SQL Server 1 to SQL Server 2.  Does someone know how to do it? Please help! In Enterprise Manager, right click on the table, go to "All Tasks", "Export Data", then follow the steps in the wizard....

How can i import records from a sql server table in project1 to another sql server table in project2
Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same. One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to ...

sql server and sql server express
ok i am puzzled right now....i have a web application connecting to a sql server database....i uninstalled sql server express and installed sql server 2005 full version...i changed the connection string in my application to be Data Source=SKYBANDR62;Initial Catalog=aspnetdb;Integrated Security=True and through the explorer bar i can access my database freely but when i run my application i get this error Server Error in '/' Application. -------------------------------------------------------------------------------- An error has occurred while establishing a...

Inserting SQL statements into a SQL Server 2000 database
Hi all Am attempting to create an audit table that will allow me to keep tracke of who is doing what where and when. On thing that i want to insert into this table, is the actual command that was executed, however when i look at the data that i inserted all that i get is "System.Data.SqlClient.SqlCommand" in the field. Any suggestion on how to overcome this problem. Mike55 Hello Mike55 if I understand you right, you like to record each request to have the possibility to control who do what and when. I think a good possibility is to create a httphandler to record all...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Relational Tables are not relational after Exported from my sql server to host sql server
hello, I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question:  relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting? thanks for your helps...Amateur Web Designer and Programmer In the Wizard steps are you specifying the keys? this dialog box will come when you uncheck the "Use default options" checkbox and click on options buttonscase when an...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Web resources about - SQL Server insert query - Checking if a sql server value is an integer before inserting into a table. If it is not, then replacing it with 00000000 - asp.net.sql-datasource

Inserting Kate
jurvetson posted a photo: the speaking GPS avatar that we came to love.. more prep photos below... You can hear her voice in the video compilation ...

Inserting the Galaxy Note 5’s S Pen backwards can permanently damage the device
One of the features Samsung highlighted when it unveiled the Galaxy Note 5 was the device's new S Pen slot design. The Note 5 features a spring-loaded ...

Inserting Slavery Into The Climate Debate
Chris Hayes compares the fight against fossil fuels to the abolitionist movement. He states plainly that “there is absolutely no conceivable ...

Samsung smart TVs inserting ads into third-party apps
... playback have pissed off a number of consumers. People with smart TVs from Samsung have been complaining that the electronics maker is inserting ...

Inserting images into Gmail could be so much better
When Google first launched the new compact compose window in Gmail, many of you might have noticed something annoying about inserting images. ...

Gawker tells Steven Crowder to "stop whining, take your licks, and accept that getting hit in the face ...
"... in the middle of an argument between billionaire-funded know-nothing ideologues and people whose livelihoods and stability are being threatened ...

Gmail's New Interface for Inserting Photos
Gmail has a new interface for adding images to a message. When you click the "insert images" icon, Gmail now shows all the photos highlighted ...

Now Samsung SmartTVs are interrupting users' videos by inserting random ads
... room. Now Samsung is dealing with a new headache: Gigaom reports that Samsung SmartTVs are interrupting users' own video content by inserting ...

Samsung SmartTVs are inserting ads into movies - Business Insider
Random Pepsi pop-up ads have been appearing when SmartTV users watch content through the device's third-party apps.

Resources last updated: 12/8/2015 1:43:27 AM