Monday, April 19, 2021

How to get only numbers from string sql

I have column where I need to get only numbers form the string.

Here is how we can do,

SUBSTRING(columnName, PATINDEX('%[0-9]%', columnName), LEN(columnName))

Here's the example with PATINDEX

use databasego
-- inventory Group, Employee , cost center
select [Cost Center], [Inventory Group], Employee, 
SUBSTRING(Employee, PATINDEX('%[0-9]%', Employee), LEN(Employee)) AS EmployeeID
from  tmp_BulkOrder_SP_4_16_21

Hope this helps 😀