how to extract string after any character

Question

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

Yitzhak Khabinsky · Answer

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

If there are some additional characters to take care of, just add them to the @junkChars variable.

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'),
('Yet-aonther-badly-written-question'), 
('*followed*by*mediocre*answers'), 
('at/the\best'), 
('Quite depressing'), 
('isn''t it?'), 
('Yep');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '_'
   , @junkChars VARCHAR(10) = '-*\ ';;

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

Output

ID	tokens	result
1	BOB_LOU_K	K
2	BOB_LOU-K	K
3	Yet-aonther-badly-written-question	question
4	followedbymediocreanswers	answers
5	at/the\best	best
6	Quite depressing	depressing
7	isn't it?	it?
8	Yep	Yep

DouadyRabbit · Answer

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.

David דודו Markovitz · Answer

select txt
      ,right(txt, patindex('%[^a-zA-Z]%', reverse(txt) + ' ') - 1) as last_token
  
from   (values ('BOB_LOU_K'), ('BOB_LOU-K'), ('Yet-aonther-badly-written-question'), ('*followed*by*mediocre*answers'), ('at~best'), ('Quite depressing'), ('isn''t it?'), ('Yep')) t(txt)

txt	last_token
BOB_LOU_K	K
BOB_LOU-K	K
Yet-aonther-badly-written-question	question
followedbymediocreanswers	answers
at~best	best
Quite depressing	depressing
isn't it?
Yep	Yep

Fiddle

how to extract string after any character

3 Answers