How do I parse a field with multiple words and look each word up in a different table?

Question

I have a table RxDir that contains columns RxNum and Dir. Dir is comprised of user-entered text that is a combination of quick codes and free text. The quick codes are stored in a table called SCodes under column SCode.

I need to create an expanded result of the directions when given RxNum. So if Dir contains

LYLAS BUT OMG YOU HAVE VD & BEANS

Then I need to have the results interpreted as

LOVE YOU LIKE A SISTER BUT OH MY GOODNESS YOU HAVE VIENNA DOGS & BEANS

(Assuming that the obvious initials are stored in SCodes and the full words are not).

I have not; wasn't sure where to begin. When I needed the info in the past, I've been able to just settle for the "un-expanded" view of the directions. Been researching but hadn't came across recursive CTE. I will check it out. — posternutbag, Jul 07 '15 at 22:34
this is a pretty good example http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example — Holmes IV, Jul 07 '15 at 22:37
Thanks Holmes. wewesthemenace, sorry- LYLAS is in the SCodes table, the meaning is in the ExTxt column. — posternutbag, Jul 08 '15 at 00:27
Do you ever have a situation of composite phrases? Where you have two or more words in the SCODE column? — Brad D, Jul 08 '15 at 02:10
Great question, Brad; sorry I didn't mention this originally. No, I don't believe so. Lots of random characters across the values, but no spaces that I can see. — posternutbag, Jul 08 '15 at 16:34

score 3 · Accepted Answer · answered Jul 08 '15 at 00:37

The first thing you want is a string splitter. Here is the DelimitedSplit8K, one of the fastest splitter there is.

CREATE FUNCTION [dbo].[DelimitedSplit8K](
    @pString VARCHAR(8000), @pDelimiter CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,E4(N) AS (SELECT 1 FROM E2 a, E2 b)
,cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
,cteStart(N1) AS(
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString, t.N, 1) = @pDelimiter
),
cteLen(N1, L1) AS(
SELECT 
    s.N1,
    ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1),0) - s.N1, 8000)
FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l

Now, what you want is to split each Dir with ' ' as the delimiter. Using your sample data and the function above

SELECT * FROM dbo.DelimitedSplit8K('LYLAS BUT OMG YOU HAVE VD & BEANS', ' ')

The result is:

ItemNumber  Item
----------- ----------
1          LYLAS
2          BUT
3          OMG
4          YOU
5          HAVE
6          VD
7          &
8          BEANS

Using the above result, you can now do a LEFT JOIN on SCodes to get the meaning or ExTxt of each Item. Then you concatenate the ExTxt back to achieve the desired result.

Your FINAL QUERY will be:

SELECT
    rd.RxNum,
    rd.Dir,
    Result = 
        STUFF((
            SELECT ' ' + ISNULL(sc.ExTxt, x.Item)
            FROM dbo.DelimitedSplit8K(rd.Dir, ' ' ) x
            LEFT JOIN SCodes sc
                ON sc.SCode = x.Item
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),        
        1, 1, '')
FROM RxDir rd

RESULT

| RxNum |                               Dir |                                                                Result |
|-------|-----------------------------------|-----------------------------------------------------------------------|
|     1 | LYLAS BUT OMG YOU HAVE VD & BEANS | LOVE YOU LIKE A SISTER BUT OH MY GOODNESS YOU HAVE VIENA DOGS & BEANS |

Note: See this article by Aaron Bertrand for other string splitters.

SQL Fiddle

How do I parse a field with multiple words and look each word up in a different table?

1 Answers1