Like statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Like statement

Author

Topic

4u.Kishan
Starting Member

1 Post

Posted - 2009-08-13 : 05:33:39

thr is table name table1 with to field ,Id and Field1 .where field1 contain text message like
id field1
1 our language is english
2 we talk in english and write in english
3 english is national language

select * from table1 where field1 like '%English%'
But i want result like
1 our language is english
2 we talk in english and write in english
2 we talk in english and write in english
3 english is national language
how to solve it please help me

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-13 : 06:27:11

huh?what you mean?izit 2 english in the row will display the row twice?

Hope can help...but advise to wait pros with confirmation...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 06:33:29

You might be able to do that with FULLTEXT (I'm not sure, never used it)

Here's a CTE that does the job though.


DECLARE @searchString VARCHAR(50)      SET @searchString = 'English'

DECLARE @sample TABLE (
	[ID] INT
	, [text] NVARCHAR(MAX)
	)

INSERT @sample ([ID], [text])
      SELECT 1, 'our language is english'
UNION SELECT 2, 'we talk in english_ and write in english'
UNION SELECT 3, 'english is national language'
UNION SELECT 4, NULL
UNION SELECT 5, 'Contains no reference to the search string'
UNION SELECT 6, ''

SELECT * FROM @sample


;WITH patrep AS
	(
	-- Anchor
	SELECT
		[ID] AS [Id]
		, [text] AS [FullText]
		, RIGHT([text], LEN([text]) - PATINDEX('%' + @searchString + '%', [text]) - LEN(@searchString) + 1) AS [slice]
	FROM
		@sample
	WHERE
		PATINDEX('%' + @searchString + '%', [text]) > 0

	-- Recusive
	UNION ALL SELECT
		[ID] AS [Id]
		, [FullText] AS [FullText]
		,  RIGHT([slice], LEN([slice]) - PATINDEX('%' + @searchString + '%', [slice]) - LEN(@searchString) + 1) AS [slice]
	FROM
		patrep
	WHERE
		PATINDEX('%' + @searchString + '%', [slice]) > 0
	)
SELECT
	[ID]
	, [FullText]
FROM
	patrep
ORDER BY
	[Id] ASC

Results:


ID	FullText
1	our language is english
2	we talk in english_ and write in english
2	we talk in english_ and write in english
3	english is national language

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-13 : 07:31:17

Or you could try using REPLACE and a Number table.

-- *** Test Data ***
DECLARE @t TABLE
(
    TId int NOT NULL
    ,Field1 varchar(255) NOT NULL
)
INSERT INTO @t
SELECT 1, 'our language is english' UNION ALL
SELECT 2, 'we talk in english and write in english' UNION ALL
SELECT 3, 'english is national language'
-- *** End Test Data ***

DECLARE @SearchText varchar(50)
SET @SearchText = 'English'

SELECT TId, Field1
FROM
(
    SELECT TId, Field1
        ,(LEN(Field1) - LEN(REPLACE(Field1, @SearchText, ''))) / LEN(@SearchText) AS SCount
    FROM @t
    WHERE Field1 LIKE '%' + @SearchText + '%'
) D
    JOIN -- use a number table. Just putting inline to show results.
    (
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5
    ) N (N)
        ON N.N <= D.SCount

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 07:56:03

Hi Ifor,

That's a solution I would never have thought of. Nice one.

However, you need to include a number table big enough to cope with all possible numbers of repetitions.

For example


-- *** Test Data ***
DECLARE @t TABLE
(
    TId int NOT NULL
    ,Field1 varchar(255) NOT NULL
)
INSERT INTO @t
SELECT 1, 'our language is english' UNION ALL
SELECT 2, 'we talk in english and write in english English English Eglish English English English English' UNION ALL
SELECT 3, 'english is national language'
-- *** End Test Data ***

DECLARE @SearchText varchar(50)
SET @SearchText = 'English'

SELECT TId, Field1
FROM
(
    SELECT TId, Field1
        ,(LEN(Field1) - LEN(REPLACE(Field1, @SearchText, ''))) / LEN(@SearchText) AS SCount
    FROM @t
    WHERE Field1 LIKE '%' + @SearchText + '%'
) D
    JOIN -- use a number table. Just putting inline to show results.
    (
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5
    ) N (N)
        ON N.N <= D.SCount

Will still only return 5 results for ID 2 even though there are 8 copies of the string.

(of course the CTE is limited as well - -the maximum number of allowed recursions is 32767).

I'm not sure which would be faster. I think ifor's is faster for this small test. It would be interesting to see on a larger dataset.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 08:32:46

Hi try this once,


DECLARE @SAMPLE TABLE ( ID INT,FIELD1 VARCHAR(64)) 
INSERT INTO @SAMPLE SELECT 1, 'OUR LANGUAGE IS ENGLISH'
INSERT INTO @SAMPLE SELECT 2, 'WE TALK IN ENGLISH AND WRITE IN ENGLISH'
INSERT INTO @SAMPLE SELECT 3, 'ENGLISH IS NATIONAL LANGUAGE'

;WITH CSVTBL(ID,I, J,FIELD1)
AS
(
	SELECT ID,0, J = CHARINDEX(' ', FIELD1+' '),FIELD1 FROM @SAMPLE
	UNION ALL
	SELECT ID,CAST(J + 1 AS INT), J = CHARINDEX(' ', FIELD1+' ', J + 1) ,FIELD1
	FROM CSVTBL
	WHERE CHARINDEX(' ', FIELD1+' ', J + 1) <> 0
)
SELECT ID,SUBSTRING(FIELD1, C.I, C.J-I) AS FIELD1 INTO #TEMP FROM CSVTBL C
ORDER BY ID

SELECT S.ID,FIELD1 FROM @SAMPLE S
INNER JOIN (SELECT ID, SUM(PATINDEX('%,' + FIELD1+',%', ',' + 'ENGLISH' + ',')) AS NUM 
FROM #TEMP GROUP BY ID ) T ON T.ID = S.ID
INNER JOIN MASTER..SPT_VALUES M ON M.TYPE ='P'
WHERE M.NUMBER < NUM

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 08:37:47

(Or) try this

SELECT ID, FIELD1, SUM(PATINDEX('%,' + FIELD2+',%', ',' + 'ENGLISH' + ',')) AS NUM
FROM #TEMP t
INNER JOIN MASTER..SPT_VALUES M ON M.TYPE = 'P'
GROUP BY ID , FIELD1, NUMBER
HAVING NUMBER < SUM(PATINDEX('%,' + FIELD2+',%', ',' + 'ENGLISH' + ','))
ORDER BY ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources