sqlsql-servert-sqlsql-server-2012

PATINDEX to detect letters and six numbers

SQL Server 2012.

table a

Id   name               number
1    py/ut/455656/ip     null
2    py/ut/jl/op         null
3    py/utr//grt         null

I want to retrieve the numbers

 Id   name               number
1    py/ut/455656/ip     455656
2    py/ut/jl/op         null
3    py/utr//grt         null

here the sql script

update table a 
set number=SUBSTRING(name,PATINDEX('py/u/[0-9]',name)+6,6)

I need to retrieve the number after py/ut and before the / . The script works well if there is a number. For the second row it is delivering jl/op

The number always has 6 digits.

Solution

  • Simply add a where clause:

    update table a 
    set number = SUBSTRING(name, PATINDEX('py/u/[0-9]', name) + 6, 6)
    where name like '%py/u/[0-9]%'