TSQL Using SUBSTRING PATINDEX and STUFF to Amend Data

Question

TSQL MSSQL 2008r2

I need help to amend data. I've got so far and now I need help.

Sample Data

[EDIT] Additonal examples added

DECLARE @Table TABLE (NodePropertyValue NVARCHAR(50))
INSERT INTO @Table (NodePropertyValue)
VALUES 
    (N'AA11✏AAA ZZZZ'),
    (N'CRAP BB22✏BBB'),
    (N'CC55✏CC1'),
    (N'DD66✏666'),
    (N'EE55✏EEE     ES177'),
    (N'RUBBISH FF22✏FFF XXXXXX'),
    (N'NONSENSE')

I want to show the data like so. If NCHAR(9999) or pencil exists and the next 3 characters are letters then add a slash (/) after the third character. If any other characters exist after the added slash then delete them. So for [AA11✏AAA ZZZZ] should be updated to [AA11✏AAA/].

If NCHAR(9999) exists and there are characters before the preceding 4 characters then delete them. So for [CRAP BB22✏BBB] should be updated to [BB22✏BBB/] For [NONSENSE] should be shown as NULL.

This is as far as I have got. As you can see I'm stuck with adding a slash and removing characters not needed.

SELECT
     V.NodePropertyValue 'Orignal'
    ,CASE   --Pencil NCHAR(9999) exists
        WHEN PATINDEX('%'+NCHAR(9999)+'%', UPPER(V.NodePropertyValue)) > 0
            THEN
                CASE 
                    WHEN --FIRST 4 chars match XX11 and 5th char equals NCHAR(9999) 
                        PATINDEX('[A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue)) > 0 
                        AND SUBSTRING(V.NodePropertyValue, PATINDEX('%[A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4, 1) = NCHAR(9999)
                    THEN 
                        STUFF(V.NodePropertyValue, PATINDEX('[A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4
                            , 50
                            , SUBSTRING(V.NodePropertyValue, PATINDEX('[A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4, 50) )
                    WHEN --Any 4 chars match XX11 and preceding char is space and 5th char equals NCHAR(9999) 
                        PATINDEX('% [A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue)) > 0 
                        AND SUBSTRING(V.NodePropertyValue, PATINDEX('%[A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4, 1) = NCHAR(9999)
                    THEN 
                        STUFF(V.NodePropertyValue, PATINDEX('% [A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4
                            , 50
                            , SUBSTRING(V.NodePropertyValue, PATINDEX('% [A-Z][A-Z][0-9][0-9]%', UPPER(V.NodePropertyValue))+ 4, 50) )
                    ELSE
                        NULL
                END
        ELSE
            NULL
    END 'Updated'
FROM
    @Table V

@ZoharPeled If you mean if that was an example then the result would be `NULL` — Mazhar, Oct 24 '17 at 10:24
I'm just asking about patterns you didn't mention. So anything other then what you describe should return null? — Zohar Peled, Oct 24 '17 at 10:28

score 1 · Answer 1 · answered Oct 24 '17 at 10:32

1

If there are always letters after the pencil and no numbers, does this suffice?

select  case when patindex('%' + nchar(9999) + '%' , NodePropertyValue)=0 then null
            else substring( NodePropertyValue, patindex('%' + nchar(9999) + '%', NodePropertyValue)-4, 8) + '/'
        end as StringStart
from @Table

answered Oct 24 '17 at 10:32

cloudsafe

2,444
1
8
24

Thanks, however, there can be numbers or a mixture of letters and numbers after the pencil. In either case, this should result in `NULL` – Mazhar Oct 24 '17 at 10:37

Zohar Peled · Accepted Answer · 2017-10-24T11:03:08.313

Here is a way to get your desired results:

Create and populate sample table (I've added some more sample data based on our conversation in the comments)

DECLARE @Table TABLE (NodePropertyValue NVARCHAR(50))
INSERT INTO @Table (NodePropertyValue)
VALUES 
(N'AA11✏AAA ZZZZ'),
(N'CRAP BB22✏BBB'),
(N'EE55✏EEE     ES177'),
(N'RUBBISH FF22✏FFF XXXXXX'),
(N'AA✏AAA ZZZZ'),
(N'AA✏A2A ZZZZ'),
(N'AA✏A'),
(N'NONSENSE')

A cte to calculate the start and end of the desired pattern

;WITH CTE AS
(
SELECT NodePropertyValue,
       -- note: there are are 4 underscores before the pencil
       PATINDEX('%____'+ NCHAR(9999) +'[a-z][a-z][a-z]%', NodePropertyValue) As startPattern, 
       CHARINDEX(NCHAR(9999), NodePropertyValue) + 3 As EndPattern
FROM @Table
)

query the cte:

SELECT  NodePropertyValue, 
        CASE WHEN startPattern > 0 THEN
            SUBSTRING(NodePropertyValue, startPattern, EndPattern-startPattern+1) + '/'
        ELSE
            NULL
        END As Updated
FROM CTE

Result:

NodePropertyValue           Updated
AA11✏AAA ZZZZ               AA11✏AAA/
CRAP BB22✏BBB               BB22✏BBB/
EE55✏EEE     ES177          EE55✏EEE/
RUBBISH FF22✏FFF XXXXXX     FF22✏FFF/
AA✏AAA ZZZZ                 NULL
AA✏A2A ZZZZ                 NULL
AA✏A                        NULL
NONSENSE                     NULL

See a live demo on rextester.

TSQL Using SUBSTRING PATINDEX and STUFF to Amend Data

2 Answers2