I have data shown below. I'd like to extract the last part after the last underscore. The data before the underscore can be any length but written with the same syntax i means : letters_letters_letters. So i wrote this code to extract the part after the last underscore then it's works perfectly but i noticed that there is 2 names written differently like (letters_letters-letters ) with - instead of _ at the end.

NAME= (SELECT SUBSTRING(''' @NAME ''', CHARINDEX(''_'',''' @NAME ''',CHARINDEX(''_'',''' @NAME ''') 1) 1, CHARINDEX(''_'',''' @NAME ''')   CHARINDEX(''_'',''' @NAME ''',CHARINDEX(''_'',''' @NAME ''') 1)) FROM TABLE)

My question is : is there away to check or extract string after any character (without specify if it's underscore or other) ?

Can anyone help please.

Column name is like :

BOB_LOU_K

the thow others columns are like :

BOB_LOU-K

Thanks

CodePudding user response：

A a minimal reproducible example is not provided. So, I am shooting from the hip.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT INTO @tbl (tokens) VALUES 
('BOB_LOU_K'),
('BOB_LOU-K');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '_'
   , @junkChars CHAR(1) = '-';

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

Output

CodePudding user response：

Create a function that finds the position of the last character that is not a letter and return the substring from that position to the end of the string. Call that function on your query, something like this SELECT Col1, [dbo].[fn_GetLastString](Col1) AS lastString FROM mytable your function fn_LastString would look somehing like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fn_GetLastString(@cSearchedExpression VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @pos INT = LEN(@cSearchedExpression)
    DECLARE @currentChar VARCHAR(1) 
    DECLARE @lastString VARCHAR(MAX)

    WHILE @pos>0
    BEGIN
        SET @currentChar = SUBSTRING(@cSearchedExpression, @pos, 1)
        IF NOT (@currentChar LIKE '[a-Z ]' )
        BEGIN
            SET @lastString = SUBSTRING(@cSearchedExpression, @pos, LEN(@cSearchedExpression) - @pos   1)
            BREAK; 
        END

    SET @pos = @pos - 1
END

RETURN @lastString
END
GO

p.s. notice the space in '[a-Z ]' that will include spaces.