CODEWITHSUNDEEP
sqlregexoracle-database
Starboi
Starboi

Reputation: 65

Finding Unicode Characters in String Field with Regex in Oracle SQL

I have a string field (comments) that contains a user id such as 'THOMASAN'. However, the string field is dynamic and can have a plethora of things written in it. But it always has the pattern 'UserID'. I am trying to use the REGEXP_SUBSTR function in Oracle SQL to pull the name out.

I have tried REGEXP_SUBSTR(comments,'[A-Z]*') but it brings back null. In a string field how do I pull out this userid?

Upvotes: 0

Views: 1710

Answers (1)

Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

UPDATE:

For the specific unicode you mentioned 

with cte as ( SELECT ' the left padding thomsan the right padding' comments FROM dual),
cte2 as (select ASCIISTR(upper(comments)) cmt from cte)

SELECT replace(regexp_substr( cmt, '\F7FD[A-Z]+', 1), 'F7FD','') userid from cte2;

Upvotes: 1

Related Questions