0

I was wondering if it is possible to write a query that returns a field only if that field contains a certain data type.

So for example, I could want to return all fields with data type VARCHAR from a search, or data type BIGINT. Not just search for fields that contain numbers or letters.

Here is an example of my psuedo code: (I know it's not very impressive):

select *
from dbo.REP_ALIAS ra
where ra.TRADE_REP [= data type integer] --integer is just an example

Is there some way to do this for all data types, not just integer.

Also: I am using T-SQL with MS SQL Server 2008 R2.

  • 1
    possible duplicate of [Checking for numeric value in field + SQL Server](http://stackoverflow.com/questions/2045430/checking-for-numeric-value-in-field-sql-server) – Popnoodles Mar 18 '14 at 15:49
  • 1
    Hmm I was asking more regarding querying for 'any' specific data type, not just numeric values. I just used an Integer value as an example. Duplicate posting was certainly not my intent. –  Mar 18 '14 at 16:09
  • 1
    The Data Type of any field in SQL is set when the field (aka column) is created. So I think you might be intending to ask whether or not a field's value can be converted (or cast) to another type? This will depend on who is doing the cnoversion, and what they are capable of: If you store the string "3/1/2014" who determines if that's a legitimate date and if it is in January or March? SQL has some rules for this, but they are sometimes different than .NET conversions – Jamie F Mar 18 '14 at 16:20
  • 1
    A field will always actually contain just one datatype, as it is defined when created (or altered.) – Jamie F Mar 18 '14 at 16:22
  • Bingo. That is the answer I was looking for. If you post it as an answer I can accept it. –  Mar 18 '14 at 16:23

4 Answers4

2

You can exclude non-digits

WHERE ra.TRADE_REP NOT LIKE '%[^0-9]%'
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • This is great, thank you. Would it also work inversely to find non-numerical values? Something like this perhaps `NOT LIKE '%[^a-z]%'`? –  Mar 18 '14 at 15:50
  • Just `LIKE '%[^0-9]%'` for the inverse. (note you would need to handle the - sign if you wanted to include that) – Alex K. Mar 18 '14 at 15:52
1

You can use - 

> WHERE ISNUMERIC(field)

Source: Checking for numeric value in field + SQL Server

Community
  • 1
  • 1
RGV
  • 732
  • 3
  • 10
  • Thank you, I did not know there was a funtcion for this. Do you know if there is a matching function to find a value that is not a number? Or could I do something along the lines of `Case When ISNUMERIC(field) = null`? –  Mar 18 '14 at 15:52
  • Yes you can certainly use that. it should be `Case When ISNUMERIC(field)=1 then 'Yes' else 'No'` End – RGV Mar 18 '14 at 15:59
  • ISNUMERIC can de a bit odd, leading tabs, currency symbols, hex/octal notations are all considered numeric. – Alex K. Mar 18 '14 at 15:59
  • In that case you can use `like '%[^0-9]%'` - as stated in alex's answer. – RGV Mar 18 '14 at 16:02
  • These are all good answers, but I was wondering if there is a more standard way of finding a field with only a certain data type, based on that data type. So for example, I could want to return all fields with data type VARCHAR from a search, or data type BIGINT. Not just search for fields that contain numbers or letters. Does that make sense? I can elaborate if needed. –  Mar 18 '14 at 16:17
  • I have updated my original question to make it clearer. –  Mar 18 '14 at 16:20
  • In v2012 you would TRY_CONVERT() – Alex K. Mar 18 '14 at 16:31
0

This question was asked already on stackoverflow here: Check if a varchar is a number (TSQL)
or here Checking for numeric value in field + SQL Server

select *
from dbo.REP_ALIAS ra
where ISNUMERIC(ra.TRADE_REP) = 1
Community
  • 1
  • 1
Surrogate
  • 331
  • 3
  • 11
  • Thank you for the answer but I was asking about generally querying for data types, not just numeric values. I only used Numeric values as an example. I have updated my OP to make it more clear. –  Mar 18 '14 at 16:21
0

The Data Type of any field in SQL is set when the field (aka column) is created. So I think you might be intending to ask whether or not a field's value can be converted (or cast) to another type.

This will depend on who is doing the conversion, and what they are capable of: If you store the string "3/1/2014" who determines if that's a legitimate date and if it is in January or March? SQL has some rules for this, but they are sometimes different than .NET conversions.

And if the field was set up as a string (NVARCHAR or VARCHAR typically) it can only contain a string and not a true datetime value. This is the source of many hacks in SQL but the best answer is to get the field set to the right datatype. Sometimes this involves multiple fields, almost redundant, for different types of data.

About half way down this page is a chart with the SQL native types and which can be converted to others: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Jamie F
  • 23,189
  • 5
  • 61
  • 77