/*===========================================================
* First variant
* 
*==========================================================*/
CREATE TABLE #Strings (
 ID INT,
 String NVARCHAR(32),
 Numbers NVARCHAR(32)
)

INSERT INTO #Strings VALUES (1, 'String1', '1, 2, 4')
INSERT INTO #Strings VALUES (2, 'String2', '4')
INSERT INTO #Strings VALUES (3, 'String3', '1, 3, 0')
INSERT INTO #Strings VALUES (4, 'String4', '4, 0')

SELECT String FROM #Strings WHERE Numbers LIKE N'%'+'4'+N'%'

DROP TABLE #Strings
GO

/*===========================================================
* Second variant
* 
*==========================================================*/
CREATE TABLE #Strings (
 ID INT,
 String NVARCHAR(32),
)

CREATE TABLE #Numbers (
 ID INT,
 Number NVARCHAR(32),
)

CREATE TABLE #StringsNumbers (
 ID INT,
 StringID INT,
 NumberID INT
)

INSERT INTO #Strings VALUES (1, 'String1')
INSERT INTO #Strings VALUES (2, 'String2')
INSERT INTO #Strings VALUES (3, 'String3')
INSERT INTO #Strings VALUES (4, 'String4')
INSERT INTO #Numbers VALUES (0, '0')
INSERT INTO #Numbers VALUES (1, '1')
INSERT INTO #Numbers VALUES (2, '2')
INSERT INTO #Numbers VALUES (3, '3')
INSERT INTO #Numbers VALUES (4, '4')
INSERT INTO #StringsNumbers VALUES (1, 1, 1)
INSERT INTO #StringsNumbers VALUES (2, 1, 2)
INSERT INTO #StringsNumbers VALUES (3, 1, 4)
INSERT INTO #StringsNumbers VALUES (4, 2, 4)
INSERT INTO #StringsNumbers VALUES (5, 3, 1)
INSERT INTO #StringsNumbers VALUES (6, 3, 3)
INSERT INTO #StringsNumbers VALUES (7, 3, 0)
INSERT INTO #StringsNumbers VALUES (8, 4, 4)
INSERT INTO #StringsNumbers VALUES (9, 4, 0)

SELECT #Strings.String
FROM   #StringsNumbers
       INNER JOIN #Numbers
            ON  #StringsNumbers.NumberID = #Numbers.ID
       INNER JOIN #Strings
            ON  #Strings.ID = #StringsNumbers.StringID
WHERE  #Numbers.Number = '4'
GROUP BY
       #Strings.String

DROP TABLE #Strings
DROP TABLE #Numbers
DROP TABLE #StringsNumbers
GO