how to verify datatype of a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to verify datatype of a field

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-07 : 20:00:38

Hi,
Can anyone tell me how to verify a datatype of a field?
I want to check whether it is a varchar or not? I know for date IsDATE function exists.

Thanks in anticipation,
Mavericky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 20:04:52

to find datatype, this is enough


SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'the column'
AND TABLE_NAME = 'the table'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2012-05-07 : 20:05:39

Typically you'd do that on the front end that has more functions to verify & validate user input than at the backend..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-09 : 05:52:00

ya but if suppose in sql i type a value as 56. Now is there any way to figure out what datatype is this particular value?

mavericky
Posting Yak Master

117 Posts

Posted - 2012-05-09 : 06:43:05

or is it possible to know if a varchar field has int values inside it. for example if i say:
abcd varchar(100)
abcd = "200";
is there any way to find out if inside abcd there is an integer value?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-09 : 08:24:22

ISNUMERIC, but that has its own problems. You could parse the variable and see.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-09 : 08:38:35

If you are looking strictly for integers (i.e., digits 0-9), you could do this:

DECLARE @abcd VARCHAR(100) ;
SET @abcd = '200';
SELECT CASE WHEN @abcd NOT LIKE '%[^0-9]%' THEN 'Numeric' ELSE 'Not numeric' END

If you want to include negative integers, change '%[^0-9]%' to '%[^-0-9]%'

If you want to allow for a decimal point also change it to this:[^-.0-9]%'

Edit: Just want to also add that none of that is perfect. For example, if you allow for negative numbers and only do the check I described, '13--33--' will be considered numeric. Of course, you can add rules to check all of that, but then it becomes a project in its own merit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources