sql - TSQL String matching question -

i trying match 2 strings using tsql.

first string:      abcd dfhg kljkl

second string: abcd dfhg kljkl - 4536764

matching rule: if second string begins first string followed " - " (that is, space, dash, space) , set of numbers (and nothing else), consider match.

any ideas?

i have 2 answers you.

1. assuming firststring values not contain of characters %, _, or [, return you're asking for. not guarantee second string begins first , followed space-dash-space , number, makes sure numbers follow point onward.

   if table wide @ all, nonclustered index includes firststring , secondstring whatever other columns want selected (or they're in clustered index) make index cover query , improve performance.

   select *     strings          secondstring firststring + ' - [0-9]%'       , secondstring not firststring + ' - %[^0-9]%'; 

   i submit if firststring blank , secondstring starts ' - ' correct per specs.

2. if firststring value contain of above characters, here's 1 way handle that:

   select *     strings          left(secondstring, len(firststring) + 3) = firststring + ' - '       , len(secondstring) > len(firststring) + 3       , substring(secondstring, len(firststring) + 4, 2147483647) not '%[^0-9]%'; 

   this kind of strange territory, here, experiment version see if performs better:

   with s (    select       *,       replace(replace(replace(replace(          firststring,          '\', '\\'),          '%', '\%'),          '_', '\_'),          '[', '\[' --' comment fix wonky code colorization       ) firststringescaped    strings ) select * s    secondstring firststringescaped + ' - [0-9]%' escape '\' --'    , secondstring not firststringescaped + ' - %[^0-9]%' escape '\'; --' 

please note if want handle spaces @ end of firststring, adjustment may required (the second query uses len not handle case properly).