SQL Server REGEX with multiples spaces white-CodePudding

I'm trying to solve this problem in SQL Server. I need to get the customer's name when in the line there is the following condition:

Start with any [letter]/[numbers][space][numbers][space]

I used [A-Z]/[0-9]%, but I can't consider the space and the sequence of numbers followed by the other space

Here's the line I'm working on:

K/31209536                          9997530556                         RICARDO JOSE DE OLIVEIRA QUEIROZ

When the condition is true, I need to get the name:

RICARDO JOSE DE OLIVEIRA QUEIROZ

CodePudding user response：

As already mentioned, SQL Server doesn't support full Regex.

Here is a method based on tokenization via XML and XQuery.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (StringColumn varchar(4000));
INSERT @tbl VALUES
('K/31209536            9997530556        RICARDO JOSE DE OLIVEIRA QUEIROZ');
-- DDL and sample data population, end

DECLARE @separator CHAR(2) = SPACE(2);

SELECT t.*
   , c.value('(/root/r[last()]/text())[1]', 'VARCHAR(150)') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE(StringColumn, @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS t1(c);

CodePudding user response：

Try this:

[A-Z]\/\d \s \d \s ((\w \s?) )

The name will be on group 1.

Or using a named group (called name):

[A-Z]\/\d \s \d \s (?<name>(\w \s?) )

Check here: https://regex101.com/r/GO3CjM/1

CodePudding user response：

SQL Server doesn't have native RegEx support, but one thing you can do in SQL Server 2016 (and compatibility level 130 ):

SELECT Original, ImportantPart = REVERSE([value]) 
FROM 
(
  SELECT Original = t.StringColumn,[key],[value]
    FROM dbo.YourTableName AS t 
    CROSS APPLY OPENJSON('["'   REPLACE(
      REVERSE(LTRIM(RTRIM(t.StringColumn))), 
    '  ', '","')   '"]')
) AS j WHERE [key] = 0;

Working example in this fiddle.

These kind of gymnastics certainly betray that you should better normalize your data (e.g. why isn't the name stored in its own column?).

CodePudding user response：

Seems like a CASE expression with a little string manipulation should do the trick

Example or dbFiddle

Select CustName = case when patindex('[A-Z]/[0-9][0-9][0-9]%',SomeCol)>0
                       then right(SomeCol,charindex('  ',reverse(SomeCol))-1)
                  end
 From  YourTable