Sql Server - Filter Field Contents to Numbers Only

SQL Server - Filter field contents to numbers only

You are going to have to write a user defined function to do this. There are several ways to do this, here is one that I found with some quick Googling.

CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
  DECLARE @pos INT
  SET @Pos = PATINDEX('%[^0-9]%',@Input)
  WHILE @Pos > 0
   BEGIN
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
   END
  RETURN @Input
END

Warning: I wouldn't put this in a WHERE condition on a large table, or in a SELECT that returns millions of rows, but it will work.

Ultimately you are probably better stripping the non-numeric characters out in the UI of your app than in DB code.

How to get only numeric column values?

SELECT column1 FROM table WHERE ISNUMERIC(column1) = 1

Note, as Damien_The_Unbeliever has pointed out, this will include any valid numeric type.

To filter out columns containing non-digit characters (and empty strings), you could use

SELECT column1 FROM table WHERE column1 not like '%[^0-9]%' and column1 != ''

How to filter integer with specific numbers in sql

freq is calculated in an aggregation query, so you need having. It is a number, so use arithmetic rather than string operations -- the appropriate one is the modulo operator, which usually uses %:

SELECT name, SUM(count) AS freq
FROM [baby.baby_names]
WHERE gender = 'M' AND year < 2010
GROUP BY name
HAVING freq % 100 = 0
ORDER BY freq DESC;

Some databases use the mod() function instead: mod(freq, 100) = 0.

Filter the rows with number only data in a column SQL

I just tried to correct the mistakes of you and made the SQL simple as possible. But not neat!

WITH dummy_data AS
  ( SELECT '-1.0' AS txt FROM dual
  UNION ALL
  SELECT '+.0' FROM dual
  UNION ALL
  SELECT '-.1' FROM dual
  UNION ALL
  SELECT '+1,2034.89.0' FROM dual
  UNION ALL
  SELECT '+1,2034.89' FROM dual
  UNION ALL
  SELECT 'Deva +21' FROM dual
  UNION ALL
  SELECT 'DeVA 234 Deva' FROM dual
  UNION ALL
  SELECT '1023' FROM dual
  )
SELECT to_number(REPLACE(txt,',')),
  REGEXP_COUNT(txt,'.')
FROM dummy_data
WHERE REGEXP_LIKE (txt,'^[-+]*')
AND NOT REGEXP_LIKE (TRANSLATE(txt,'+,-.','0000'),'[^[:digit:]]')
AND REGEXP_COUNT(txt,',')  <= 1
AND REGEXP_COUNT(txt,'\+') <= 1
AND REGEXP_COUNT(txt,'\-') <= 1
AND REGEXP_COUNT(txt,'\.') <= 1;

How to FILTER for string in numeric column

You can use an rlike filter as below:

df.filter("RefId NOT RLIKE '^[0-9]+$'").show()
+-------+
|  RefId|
+-------+
|RefNum2|
+-------+

Or

import pyspark.sql.functions as F

df.filter(~F.col("RefId").rlike("^[0-9]+$")).show()
+-------+
|  RefId|
+-------+
|RefNum2|
+-------+

Filter records based on numeric value when column type is varchar

Select * 
 From  dbo.mytable 
 Where os_ver<='5.0.0'

Returns

os_ver
5.0.0
4.0.2
2.3.4-ez
4.2.2-2013-12-11-V1.0
4.6.0.304
2.1-update1
2.3

Filter by numeric column start with a given number

If the number is in a particular range, then you could do something like:

where num_column >= 2045000 and num_column < 2046000

I am guessing that is not the case . . . although you could extend this:

where (num_column >= 2045 and num_column < 2046) or
      (num_column >= 20450 and num_column < 20460) or
      (num_column >= 204500 and num_column < 204600) or
      . . .

I'm not sure if Oracle would really use an index for a complicated or.

There is another solution. Create an index on an expression and use the expression:

create index idx_mytable_numcolumn_str on my_table(to_char(num_column));

Then you can do:

where to_char(num_column) like '2045%'

How does one filter based on whether a field can be converted to a numeric?

It seems that you are gonna have problems with the ISNUMERIC function, since it returns 1 if can be cast to any number type (including ., ,, e0, etc). If you have numbers longer than 2^63-1, you can use DECIMAL or NUMERIC. I'm not sure if you can use PATINDEX to perform an regex look on SummaryInvoice, but if you can, then you should try this:

SELECT SummaryInvoice
FROM Invoice
WHERE ISNULL(SummaryInvoice, '') <> ''
AND CASE WHEN PATINDEX('%[^0-9]%',SummaryInvoice) > 0 THEN CONVERT(DECIMAL(30,0), SummaryInvoice) ELSE -1 END
BETWEEN @StartSummary And @EndSummary

---

Related Topics