Search string for matching pattern

Question

Search string for matching pattern

karen.ferrara

Right there with Babe

Points: 770
More actions
October 7, 2016 at 6:53 am

#321995

I thought this would be fairly simple but it has not been. I am using TSQL to search for a string pattern like 'dog.cat' or 'right,wrong' where there is a period in the middle and no numbers or special characters. but my query is not working:
I have the following data in my table. Only Sam.Smith and Michael.Jackson should be valid:
TEST
TEST.
.TEST
.TES.T
8Dogn.cat8
Dog8.cat
Dog8.cat
Dog@.cat
ttest.TES.T
Sam.Smith
Michael.Jackson
Can anybody help me please?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Sure! Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

-- Gianluca Sartori

karen.ferrara Right there with Babe Points: 770 More actions · Answer 2

Thank you. The following is my script. In the last query I want only rows where the login name is Sam.Smith or Michael.Jackson.

CREATE TABLE #tmp (loginNameVARCHAR(50))

INSERT INTO #tmp (loginName)

VALUES

('TEST'),

('TEST.'),

('.TEST'),

('.TES.T'),

('8Dogn.cat8'),

('Dog8.cat'),

('Dog@.cat'),

('ttest.TES.T'),

('Sam.Smith'),

('Michael.Jackson')

SELECT * FROM #tmp -- get all rows

-- filter out

SELECT * FROM #tmp WHERE loginName LIKE '%[^a-z.A-Z]%'

or LoginName LIKE '.%'

or LoginName LIKE '%.'

or LEN(LoginName) - LEN(REPLACE(loginName,'.','')) <> 1

-- include

SELECT * FROM #tmp WHERE loginName LIKE '%[a-z.A-Z]%'

and LoginName NOT LIKE '.%'

and LoginName NOT LIKE '%.'

and LEN(LoginName) - LEN(REPLACE(loginName,'.','')) = 1

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here's a query with three conditions that should fulfill your requirements.

SELECT *

FROM #tmp

WHERE loginName LIKE '%_._%' --Has a period surrounded by other characters

AND loginName NOT LIKE '%.%.%' --Has only one period

AND loginName NOT LIKE '%[^a-zA-Z.]%'; --Does not contain characters other than letters and periods

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Luis Cazares (10/7/2016)
Here's a query with three conditions that should fulfill your requirements.
SELECT *
FROM #tmp
WHERE loginName LIKE '%_._%' --Has a period surrounded by other characters
AND loginName NOT LIKE '%.%.%' --Has only one period
AND loginName NOT LIKE '%[^a-zA-Z.]%'; --Does not contain characters other than letters and periods

As long as loginName is case insensitive, you can simplify this even further.

SELECT *

FROM #tmp

WHERE loginName LIKE '%_._%' --Has a period surrounded by other characters

AND loginName NOT LIKE '%.%.%' --Has only one period

AND loginName NOT LIKE '%[^a-z.]%'; --Does not contain characters other than letters and periods

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA