String manipulation (searching)

Recently I have seen some post in the news group regarding seraching a string in the table or even part of string. Yes , there are lots of resources in the internet how to do that. I would like to shere some ideas also here.


Consider the following structure. We need to find in col1 a part of string. Let us find occurence of '23'.

CREATE TABLE #Test
(
col INT,
col1 VARCHAR(100)
)
INSERT INTO #Test VALUES (1,'12,23,44,56')
INSERT INTO #Test VALUES (2,'26,45,22,32')
INSERT INTO #Test VALUES (3,'45,22,34,23')

DECLARE @st VARCHAR(10)
SET @st='23'
SELECT *
FROM #test
WHERE ','+col1+',' LIKE '%,'+@st+',%'


Now, take a look at following table
CREATE TABLE #Test (contact VARCHAR(20) PRIMARY KEY)

INSERT INTO #Test VALUES ('Bill Clinton')
INSERT INTO #Test VALUES ('George W. Bush')
INSERT INTO #Test VALUES ('John F Kennedy')
INSERT INTO #Test VALUES ('Smith')

1)
SELECT contact
FROM #Test
WHERE contact LIKE '%[A-Z] [A-Z][^A-Z]%[A-Z]'
----
2)
SELECT contact
FROM #Test
WHERE contact LIKE '% _ %' or contact LIKE '% _. %'

The first one returns all people that have a middle name. The second one returns
people who has not '.' in this name after middle name.