Extract Characters to the Right of a Delimited Value in a Select Statement

Extract characters to the right of a delimited value in a SELECT statement

This question has a database specific answer.

If using SQL Server:

SELECT column1
     , RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) as extracted
     , column3 
FROM myTable

You can add a CASE statement or use NULLIF() in case the hyphen isn't always present:

SELECT column1
     , CASE WHEN column2 LIKE '%-%' THEN RIGHT(column2,CHARINDEX('-',REVERSE(column2))-1) 
           END as extracted
     , column3 
FROM myTable

Or:

SELECT column1
     , RIGHT(column2,NULLIF(CHARINDEX('-',REVERSE(column2)),0)-1) as extracted
     , column3 
FROM myTable

If using MySQL just change CHARINDEX() to LOCATE(). I believe Oracle it's INSTR() and the first two parameters are switched, first it's the string you're searching in, then the string you're searching for.

How to extract characters to the right of a delimited value in a SELECT statement in Hive

You can use regular expression to extract city name. Using regular expression your query will be like this.

select regexp_extract(city, '([a-zA-Z]+)', 1) from table_name;

where regexp_extract() is a record/line extraction of the data you wish to extract. More detail about regexp_extract() is available on hive LanguageManual+UDF

Regular expression tutorial is available on this link

UPDATE1

Input Data:

Hello/Chicago
101/London
By/America

Query and regular expression to extract everything after /:

select regexp_extract(city,'.*\/(.*)',1) from tbl_name;

Output:

Chicago
London
America

SQL Select everything after character

You can use:

select right(col, charindex('-', reverse(col)) - 1)

Extract parts of string separated by delimiter

SQL Server 2016+

Concept using STRING_SPLIT(), PARSENAME(), PIVOT

-- Mimic Table named z_tbl_tmp
DECLARE @z_tbl_tmp TABLE (id INT, OPTIONAL_FIELD_1 NVARCHAR(max));
INSERT INTO @z_tbl_tmp VALUES (1, N'Growth: Compliance;Priority: Contractual;Original Vendor: ABC SERVICES;');
INSERT INTO @z_tbl_tmp VALUES (2, N'Growth: Run; Priority: Critical - Turns Contractual');
-- 

-- Pivot Parsed Data
WITH tbl_parsed AS (
    -- Parse Data into Key Value Pairs
    SELECT id, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 2)) AS K, 
        TRIM(PARSENAME(REPLACE(value,': ','.'), 1)) AS V 
    FROM @z_tbl_tmp
        CROSS APPLY STRING_SPLIT(OPTIONAL_FIELD_1,';')
)
SELECT id, [Growth] AS GROWTH_TXT, [Priority] AS PRIORITY_TXT
FROM tbl_parsed
    PIVOT (MAX(V) FOR [K] IN ([Growth], [Priority])) AS pvt

+----+------------+-------------------------------+
| id | GROWTH_TXT | PRIORITY_TXT                  |
+----+------------+-------------------------------+
|  1 | Compliance | Contractual                   |
+----+------------+-------------------------------+
|  2 | Run        | Critical - Turns Contractual  |
+----+------------+-------------------------------+

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

SQL Query to select a string after last delimiter

Use REGEXP_SUBSTR

select regexp_substr('Attachments:Attachments~Attachment','[^~]+$') from dual;

• [^ ] - Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
• + - Matches one or more occurrences
• $ - Matches the end of a string

Demo on db<>fiddle

How to get the 1st value before delimiter in sql server

You can use SUBSTRING to do this:

SELECT 
    SUBSTRING(TRN02, 0, CHARINDEX('-', TRN02)) AS [First]
    SUBSTRING(TRN02, CHARINDEX('-', TRN02)  + 1, LEN(TRN02)) AS [Second]
FROM TABLE

How do I split a delimited string so I can access individual items?

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

---

Related Topics