-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
  • 732,580
  • 175
  • 1,330
  • 1,459
Irfan
  • 665
  • 6
  • 29

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
  • 502,043
  • 27
  • 286
  • 360
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
  • 6,330
  • 2
  • 21
  • 30