Less than 2 occurances in a string

Question

Less than 2 occurances in a string

ken.trock

SSCertifiable

Points: 5147
More actions
October 25, 2012 at 1:22 pm

#283567

Hello. I don't see an easy way to return rows that have less than 2 occurances of a character within a varchar in a table. Sure, you can parse each row using CHARINDEX() but that's only ok. Here I'd be looking for any row with an incomplete <DB>.<Schema>.<Table> syntax, less than 2 periods.
DECLARE @StringTest TABLE(sString VARCHAR(100))
INSERT @StringTest
SELECT 'dbACD.Config.vwICMChildSkillGroup' UNION
SELECT 'dbACD.Detail.vwRockwellOutdials' UNION--This row is ok
SELECT 'Config.vwICMDirection' UNION--This row fails
SELECT 'dbReportSummary.ReportSummary.vwACDAgentSummary'

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Great job on providing the setup of the data.

by simply comparing the length of the string against the length of the replace of periods, you get the # of periods that occurred.

DECLARE @StringTest TABLE(sString VARCHAR(100))

INSERT @StringTest

SELECT 'dbACD.Config.vwICMChildSkillGroup' UNION

SELECT 'dbACD.Detail.vwRockwellOutdials' UNION--This row is ok

SELECT 'Config.vwICMDirection' UNION--This row fails

SELECT 'dbReportSummary.ReportSummary.vwACDAgentSummary'

SELECT * FROM @StringTest

WHERE LEN(sString) - LEN(REPLACE(sString,'.','')) <> 2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

Thanks Lowell. That is some out of the box thinking!

Ken