How to find values that contains only 0's and any other digit for example 000000001 or 00000100-CodePudding

I want to find 'default type values' in SQL that is entered when something like an ID number of company registration number is entered. Some of the values I see is a combination of 0's and another digit from 1-9. Examples I have seen is 00000001, 0000100, 000000002, 000001111, 0000090009, etc. The values vary in length also. Is there a way to find these values without hard coding?

CodePudding user response：

You want all strings that consist of only zero and one other digit. I.e. you want to find '0101', but not '0102'.

In order to do this, remove all zeros first. From the remaining string remove all digits equaling to its first character. This will result in an empty string or a string consisting of additional digits or characters. Only select those resulting in an empty string.

select *
from mytable
where replace(replace(value, '0', ''), substring(replace(value, '0', ''), 1, 1), '') = '';

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c307bbbf21ceeae619a966e995c3a567

CodePudding user response：

You can use ISNUMERIC() function

SELECT ISNUMERIC(0000004);

This will return 1

SELECT ISNUMERIC('A');

This will return 0

So if you want to select all columns that are numeric only you can use this logic:

select *
from test
where ISNUMERIC(colA) = 1

Here is a small demo

Or you can use TRY_CAST() function:

select *
from test
where try_cast(colA as int) is not null

CodePudding user response：

Alternative solution:

SELECT value
FROM mytable
CROSS JOIN (
  SELECT '1' AS num
  UNION ALL
  SELECT '2'
  UNION ALL
  SELECT '3'
  UNION ALL
  SELECT '4'
  UNION ALL
  SELECT '5'
  UNION ALL
  SELECT '6'
  UNION ALL
  SELECT '7'
  UNION ALL
  SELECT '8'
  UNION ALL
  SELECT '9'
) n
WHERE REPLACE(REPLACE(value, '0', ''), num, '') = ''
AND REPLACE(value, '0', '') <> ''
AND value LIKE '%0%'