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...
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
(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
