How to convert regexp_substr(Oracle) to SQL Server?-CodePudding

I have a data table which has a column as Acctno what is expected shows in separate column

|Acctno                | expected_output|
|ABC:BKS:1023049101    | 1023049101     |
|ABC:UWR:19048234582   | 19048234582    |
|ABC:UEW:1039481843    | 1039481843     |

I know in Oracle SQL which I used the below

select regexp_substr(acctno,'[^:]',1,3) as expected_output
from temp_mytable

but in Microsoft SQL Server I am getting an error that regexp_substr is not a built in function

How can I resolve this issue?

CodePudding user response：

We can use PATINDEX with SUBSTRING here:

SELECT SUBSTRING(acctno, PATINDEX('%:[0-9]%', acctno)   1, LEN(acctno)) AS expected_output
FROM temp_mytable;

Demo

Note that this answer assumes that the third component would always start with a digit, and that the first two components would not have any digits. If this were not true, then we would have to do more work.

CodePudding user response：

Just another option if the desired value is the last portion of the string

Select * 
      ,NewValue = parsename(replace(Acctno,':','.'),1)
 from YourTable