-1

I have a table AgentDetail, and I need to create a query which returns only records which contain left most 5 numeric digits.

The table has 3 columns 

AgentId, AgentName, AgentTextCode

where in the AgentTextCode column, there could be 5 digits or any text value (sometime 2 bytes chars). So output records should be only those which have a value which starts with 5 numeric digits (decimal value not possible).

Sample data & output:

enter image description here

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Irfan
  • 645
  • 7
  • 24

2 Answers2

2

We can use LIKE here:

SELECT
    AgentID, AgentName, AgentTextCode
FROM yourTable
WHERE AgentTextCode LIKE '[0-9][0-9][0-9][0-9][0-9]%';

SQL Server's LIKE operator supports some primitive regex capabilities, as shown above.

Tim Biegeleisen
  • 451,927
  • 24
  • 239
  • 318
0

You can use IsNumeric and Substring from TSQL:

SELECT
    AgentID, AgentName, AgentTextCode
FROM yourTable
WHERE ISNUMERIC(Replace(Replace(substring(AgentTextCode, 1, 5),'+','A'),'-','A') + '.0e0') = 1;
GO

Reference here: CAST and IsNumeric

Gauravsa
  • 5,847
  • 2
  • 16
  • 25