Parsing expression only with sql \ finding an order of expression's values

Question

Is there a way to find an order of words/letters inside an expression found in the database?

To be more clear here is an example:

From table X i'm getting the Names: "a" and "b". In other table there is the expression: "b + a", The result I need is b,1 | a,2

Is there any way to do it using only SQL query?

P.S. I didn't find any reference to this subject...

what if a letter is repeated more than once in the same expression i.e. "b * (c + a) / (b - c)". how it is given numbers? — Jatin Patel, Jul 27 '16 at 08:18

score 1 · Answer 1 · edited May 23 '17 at 12:14

(From an similar question)

You can do it with CHARINDEX() that searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found.

CHARINDEX(' a ',' ' + REPLACE(REPLACE(@mainString,'+',' '),'.',' ') + ' ')

Add more recursive REPLACE() calls for any other punctuation that may occur

For your question here is an example:

INSERT INTO t1 ([name], [index])
SELECT name, CHARINDEX(' ' + name + ' ',' ' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('b * (c + a) / (b - c)','+',' '),'-',' '),'*',' '),'(',' '),')',' '),'/',' ') + ' ')
FROM t2

The result will be:

a, 10
b, 1
c, 6

score 1 · Accepted Answer · answered Jul 27 '16 at 09:05

Beautiful question! Take a look at this solution wchich breaks expression into list of identifiers:

DECLARE @val varchar(MAX) = 'b * (c + a) / (b - c)';

WITH Split AS
(
    SELECT 1 RowNumber, LEFT(@val, PATINDEX('%[^a-z]%', @val)-1) Val, STUFF(@val, 1, PATINDEX('%[^a-z]%', @val), '')+'$' Rest
    UNION ALL
    SELECT RowNumber+1 Rownumber, LEFT(Rest, PATINDEX('%[^a-z]%', Rest)-1) Val, STUFF(Rest, 1, PATINDEX('%[^a-z]%', Rest), '') Rest
    FROM Split
    WHERE PATINDEX('%[^a-z]%', Rest)<>0
)
SELECT Val, ROW_NUMBER() OVER (ORDER BY MIN(RowNumber)) RowNumber FROM Split
WHERE LEN(Val)<>0
GROUP BY Val

It yields following results (only first occurences):

b   1
c   2
a   3

If executed with DECLARE @val varchar(MAX) = 'as * (c + a) / (bike - car)' returns:

as      1
c       2
a       3
bike    4
car     5

Parsing expression only with sql \ finding an order of expression's values

2 Answers2