Get a string from a sentence with the occurence of another String

Question

I have a table with a column Col1 which has values as below:

SELECT 'XXX-T09-CFO Signature' AS  Col1
UNION 
SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
UNION 
SELECT'ZZZ-T13-PAB Chair Signature'

My output col2 should have string before the word "Signature" from col1.If the word Signature is not found it should be set to NULL

SELECT 'XXX-T09-CFO Signature' AS  Col1,'CFO' as Col2
UNION 
SELECT 'YYY-T140 - Update Funding Authorization Status to Completed' ,Null
UNION 
SELECT'ZZZ-T13-PAB Chair Signature','PAB Chair'

Can you clarify what exactly your question is? I don't understand what you are trying to accomplish or where you are running into problems. — jack, Jan 10 '20 at 17:31
my output should have values mentioned in Col2. If the string is "'XXX-T09-CFO Signature' " ,my O/P should be first string before the keyword Signature I,e "CFO" — SuperKings, Jan 10 '20 at 17:32

score 0 · Answer 1 · answered Jan 10 '20 at 17:42

0

I think you want to use STRING_SPLIT: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15&viewFallbackFrom=sql-server-previousversions

I'll demonstrate using it once (without testing), but you could use it twice to get what you want, once splitting on the dash and once on the space

SELECT Word
FROM
    (
     SELECT 'XXX-T09-CFO Signature' AS Word,ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) RN
     FROM STRING_SPLIT(@Var, '-')
    ) T
WHERE RN == 3;

Modified from https://stackoverflow.com/a/54026051/11817085

answered Jan 10 '20 at 17:42

jack

573
4
12

No, that's wrong. First, string_split was introduced in 2016, the OP is working with 2008r2. Second, that's not reliable. `order by (select null)` will produce an arbitrary row number, and string_split returns a table with the parts of the string, but no order column. For more information, read [The “Natural order” misconception](https://zoharpeled.wordpress.com/2019/09/08/the-natural-order-misconception/) on my blog. – Zohar Peled Jan 13 '20 at 08:28

score 0 · Answer 2 · answered Jan 13 '20 at 08:45

First, you should know that 2008 and 2008r2 are no longer supported. Their extended support period ended last year - in July 9th 2019.
You should seriously consider upgrading your SQL Server version (note that 2012 is also going out of extended support in a couple of years - July 12, 2022.)

That being said, Here's a solution that will work on 2008r2 as well as on all currently supported versions - using a combination of case, charindex, right, and reverse:

WITH SourceTable AS
(
    SELECT 'XXX-T09-CFO Signature' AS  Col1
    UNION 
    SELECT 'YYY-T140 - Update Funding Authorization Status to Completed'
    UNION 
    SELECT'ZZZ-T13-PAB Chair Signature'
), CTE AS
(
    SELECT Col1,
           SUBSTRING(Col1, 0, CHARINDEX('Signature', Col1)) As BeforeSignature
    FROM SourceTable
)

SELECT  Col1, 
        CASE WHEN BeforeSignature = '' THEN 
            NULL 
        ELSE 
            RIGHT(BeforeSignature, CHARINDEX('-', REVERSE(BeforeSignature))-1)
        END As Col2
FROM CTE

Results:

Col1                                                            Col2
XXX-T09-CFO Signature                                           CFO 
YYY-T140 - Update Funding Authorization Status to Completed     NULL
ZZZ-T13-PAB Chair Signature                                     PAB Chair

Get a string from a sentence with the occurence of another String

2 Answers2