I am using SQL Server 2017.

I have a Description column in my table which may contain multiple colors. I need to grab the last color that appears in the string. Is there way to order it per value that is parsed from the description column descending?

SELECT DISTINCT color   
FROM (VALUES ('Dove'), ('Frost')) t(Color)
WHERE 'jgkgh FROST tohjgkhg DOVE gfsgfgs' LIKE CONCAT('%', t.Color, '%') 
  AND t.Color IS NOT NULL 

CodePudding user response：

Please try the following solution.

It is using XML/XQuery. Their data model is based on ordered sequences. Exactly what we need.

1. CROSS APPLY converts Description column into XML data type.
2. To get the last token, we are using the following XPath predicate: /root/r[last()]
3. To sort colors, we are using FLWOR expression with the order by data($x) descending clause.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Description] VARCHAR(255));
INSERT INTO @tbl ([Description]) VALUES
('Blue Black Red'),
('Cyan Green White');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

-- without sorting
SELECT t.*
    , c.value('(/root/r[last()]/text())[1]', 'VARCHAR(20)') AS lastColor
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE([Description], @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML)) AS tab(c);

-- with sorting
SELECT t.*
    , c.value('(/root/r[last()]/text())[1]', 'VARCHAR(20)') AS lastColor
    , c
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA['   
      REPLACE([Description], @separator, ']]></r><r><![CDATA[')   
      ']]></r></root>' AS XML).query('<root>
        {
            for $x in /root/r
            order by data($x) descending
            return $x
        }
      </root>
      ')) AS tab(c);

Output without sorting

 ---- ------------------ ----------- 
| ID |   Description    | lastColor |
 ---- ------------------ ----------- 
|  1 | Blue Black Red   | Red       |
|  2 | Cyan Green White | White     |
 ---- ------------------ ----------- 

Output with sorting

 ---- ------------------ ----------- 
| ID |   Description    | lastColor |
 ---- ------------------ ----------- 
|  1 | Blue Black Red   | Black     |
|  2 | Cyan Green White | Cyan      |
 ---- ------------------ ----------- 

CodePudding user response：

…

SELECT distinct color , charindex(' '   reverse(color) ' ', ' '   reverse('jgkgh FROST tohjgkhg DOVE gfsgfgs dove frost')   ' ') as rvrscharidx 
FROM (VALUES('Dove'),('Frost'))t(Color)
WHERE   'jgkgh FROST tohjgkhg DOVE gfsgfgs dove frost' LIKE CONCAT('%',t.Color,'%') AND t.Color IS NOT NULL 
order by rvrscharidx

CodePudding user response：

UNTESTED: Uses table value function STRING_SPLIT()

WITH CTE AS (SELECT value 
FROM STRING_SPLIT('jgkgh FROST tohjgkhg DOVE gfsgfgs', ' ', 1))

SELECT TOP 1 *
FROM CTE 
WHERE VALUE in ('Dove', 'Frost')
ORDER BY Ordinal Desc

Reference Example:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

CodePudding user response：

The simplest and fastest way to solve this:

-- DDL and sample data population, start DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Description] VARCHAR(255)); INSERT INTO @tbl ([Description]) VALUES ('Red Blue Black'), ('White Green'); -- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT t.*, LastColor = RIGHT(t.Description,CHARINDEX(SPACE(1),REVERSE(t.Description)))
FROM @tbl AS t;

Returns:

ID   Description                                                                                                                                                                                                                                                     LastColor
---- ------------------
1    Red Blue Black                                                                                                                                                                                                                                                   Black
2    White Green                                                                                                                                                                                                                                                      Green