IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
        IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

        CREATE TABLE #TABLE1
        (
            CODE_NAME_T1 NVARCHAR(20)
        )

        CREATE TABLE #TABLE2
        (
            CODE_NAME_T2 NVARCHAR(20)
        )

        INSERT INTO #TABLE1(CODE_NAME_T1)
        VALUES             ('BBX123')
                          ,('B/X230')
                          ,('1BC030')
                          ,('BB01BC')
                          ,('BN01BB')           

        INSERT INTO #TABLE2(CODE_NAME_T2)
        VALUES             ('B')
                          ,('BB')

        --SELECT 1
        SELECT T1.CODE_NAME_T1
            ,'CODE_NAME_T2' = (SELECT TOP 1 T2.CODE_NAME_T2
                               FROM #TABLE2 T2
                               WHERE T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%')
        FROM #TABLE1 T1

        --SELECT 2
        SELECT T1.CODE_NAME_T1
              ,T2.CODE_NAME_T2
        FROM #TABLE1 T1
        LEFT OUTER JOIN #TABLE2 T2
        ON T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%'

        IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
        IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

RESULTS

SELECT 1

CODE_NAME_T1   |    CODE_NAME_T2
---------------|----------------
BBX123         |    B
B/X230         |    B
1BC030         |    B
BB01BC         |    B
BN01BB         |    B

SELECT 2

CODE_NAME_T1   |    CODE_NAME_T2
---------------|----------------
BBX123         |    B
BBX123         |    BB
B/X230         |    B
1BC030         |    B
BB01BC         |    B
BB01BC         |    BB
BN01BB         |    B
BN01BB         |    BB

in above code I am trying to get correct value in second column from #TABLE2 and tried two SELECT approaches but not getting the result which I am after.

ASK:
Basically I need code which should look in #TABLE1 values and if any character appearing once or twice then it should bring the correct value from #TABLE2. Like above in first result in second column all values are “B” but it should be “BB” for “BBX123” because here the “B” character is appearing twice and it should “B” for “B/X230” because here “B” character is appearing once.

So complete result should be.

Result required

CODE_NAME_T1   |    CODE_NAME_T2
---------------|----------------
BBX123         |    BB
B/X230         |    B
1BC030         |    B
BB01BC         |    BB
BN01BB         |    BB

Is there any way to achieve this? Do I need to do some kind of character counting?

Note- that “B” character is an example here as it can be any character which appearing once or twice in #TABLE1

You can use query like this

select CODE_NAME_T1,
case  when charindex('BB', code_name_t1)>0 then 'BB'
      when (charindex('B',CODE_NAME_T1)>0 and charindex('BB', code_name_t1)=0) then 'B' else NULL end as 'B'
 from #TABLE1

then you can join with your second table to get other details

more