I am trying to match columns from 2 tables in a CASE statement, while ignoring the third character in the string.

Sample data:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2

CREATE TABLE #t1
 (      courseid     VARCHAR(10) 
 )

 INSERT INTO #t1(
 courseid
 )
 VALUES
     (00.123456)
    ,(01.234567)
    ,(02.345678)

 CREATE TABLE #t2
 (
      courseid     VARCHAR(10)
 )

 INSERT INTO #t2(
    courseid
    )
 VALUES
     (00.923456)
    ,(01.834567)
    ,(02.745678)

Is the following a reliable way to do matching?

(I don't need the 3rd select statement to work, just using it as a reference to show you how I'm using my case statement. It works in my actual code, just wondering if using Right/Left is a reliable way to do that, or if there is a better way with a wildcard or something.)

SELECT 
t1.courseid
,LEFT(t1.courseid,2) right(t1.courseid,5) AS T1_CourseID FROM #t1 AS t1
SELECT 
t2.courseid
,LEFT(t2.courseid,2) right(t2.courseid,5) AS T2_CourseID FROM #t2 AS t2


 SELECT    
 CASE WHEN EXISTS (SELECT t1.CourseID
                        FROM #T1 AS t1
                       WHERE LEFT(t1.courseid,2) RIGHT(t1.courseid,5)= LEFT(t2.courseid,2) RIGHT(t2.courseid,5)) THEN 1 ELSE 0 END IsMatchedCourse
FROM #t1 AS T1

Results:

CodePudding user response：

You can leverage STUFF() function for your needs.

Please see below a conceptual example.

SQL

DECLARE @courseid VARCHAR(10)  = '00.123456';

SELECT @courseid AS [Before]
    , STUFF(@courseid, 4, 1, '') AS [After]

Output