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'

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 25, 2012 at 1:33 pm

  #1552912

  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

  October 25, 2012 at 1:40 pm

  #1552915

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

  Ken