substring - TSQL find records with different writing in another table -


i have "mastertable" following records:

mastertable:

col1 px02894 px02895 px02896 px02897/98 

from lookup table want col2 links, keeping formatting of mastertable, represented output table below.

lookuptable:

col1            col2 px02894-px02895 link001 px02896         link002 px02897-px02898 link003 

outputtable:

col1         col2 px02894      link001 px02895      link001 px02896      link002 px02897/98   link003 

as can see writing different "/" , "-".

i've tried

len(col1) > 7 left(col1,5) + right(col1,2)

but that's wrong. need union first?

here's fiddle

what need here? in advance.

select m.col1,l.col2 mastertable m inner join linktable l on (substring(m.col1,1,7) = substring(l.col1,1,7)) or (substring(m.col1,1,7) = substring(l.col1,9,7)) 

should long can trust data formating. if not few more checks e.g

substring(l.col1,8,1) = '-' 

Comments

Popular posts from this blog

c# - Operator '==' incompatible with operand types 'Guid' and 'Guid' using DynamicExpression.ParseLambda<T, bool> -