?? on Finding records with caharacters before and after in a string??

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  September 29, 2017 at 8:26 am

  #328842

  Hi

  Looking to find record that contain characters berfore and after and ignore the ones in the middle.

  For example

  Here are two records
   looking to pickup anything that contains '1NNNNNN202'
  where I only want to look at the 1 and 202
  So the example "I 305|S 166|C 001 008 411|1 2408 202"  <- I Want
  but "|I 305|S 166|C 001 008|1 2026 243" <- I dont want
  but picking up the second record with  like '%201%' because of "|I 305|S 166|C 001 008|1 2026 243"
  and not always in the same place for example "|I 305|S 166|C 001 411|1 7100 202|2 7101 175"

  Thanks
  Joe

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  September 29, 2017 at 8:37 am

  #1961664

  jbalbo - Friday, September 29, 2017 8:26 AM

  Hi

  Looking to find record that contain characters berfore and after and ignore the ones in the middle.

  For example

  Here are two records
   looking to pickup anything that contains '1NNNNNN202'
  where I only want to look at the 1 and 202
  So the example "I 305|S 166|C 001 008 411|1 2408 202"  <- I Want
  but "|I 305|S 166|C 001 008|1 2026 243" <- I dont want
  but picking up the second record with  like '%201%' because of "|I 305|S 166|C 001 008|1 2026 243"
  and not always in the same place for example "|I 305|S 166|C 001 411|1 7100 202|2 7101 175"

  Thanks
  Joe

  I'm not completely clear on the rules for what you want to keep, but it looks like you want to split the string into parts and extract only the parts that meet a specific pattern.  There's an excellent article at http://www.sqlservercentral.com/articles/String+Manipulation/94365/ that covers that exact topic.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 29, 2017 at 8:45 am

  #1961670

  jbalbo - Friday, September 29, 2017 8:26 AM

  Hi

  Looking to find record that contain characters berfore and after and ignore the ones in the middle.

  For example

  Here are two records
   looking to pickup anything that contains '1NNNNNN202'
  where I only want to look at the 1 and 202
  So the example "I 305|S 166|C 001 008 411|1 2408 202"  <- I Want
  but "|I 305|S 166|C 001 008|1 2026 243" <- I dont want
  but picking up the second record with  like '%201%' because of "|I 305|S 166|C 001 008|1 2026 243"
  and not always in the same place for example "|I 305|S 166|C 001 411|1 7100 202|2 7101 175"

  Thanks
  Joe

  
SELECT YT.YourField
FROM YourTable AS YT
WHERE YT.YourField LIKE '1%202'


  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 29, 2017 at 8:53 am

  #1961675

  sgmunson - Friday, September 29, 2017 8:45 AM

  jbalbo - Friday, September 29, 2017 8:26 AM

  Hi

  Looking to find record that contain characters berfore and after and ignore the ones in the middle.

  For example

  Here are two records
   looking to pickup anything that contains '1NNNNNN202'
  where I only want to look at the 1 and 202
  So the example "I 305|S 166|C 001 008 411|1 2408 202"  <- I Want
  but "|I 305|S 166|C 001 008|1 2026 243" <- I dont want
  but picking up the second record with  like '%201%' because of "|I 305|S 166|C 001 008|1 2026 243"
  and not always in the same place for example "|I 305|S 166|C 001 411|1 7100 202|2 7101 175"

  Thanks
  Joe

  
SELECT YT.YourField
FROM YourTable AS YT
WHERE YT.YourField LIKE '1%202'


  Or if it's a single column with multiple values (terrible choice for the design).

  
WITH SampleData AS(
  SELECT 'I 305|S 166|C 001 008 411|1 2408 202' AS String
  UNION ALL
  SELECT '|I 305|S 166|C 001 008|1 2026 243'
)
SELECT *
FROM SampleData
WHERE '|' + String + '|' LIKE '%|1%202|%';


  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

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  September 29, 2017 at 9:00 am

  #1961676

  I'm not entirely sure of your intended search rules either, but as best I can tell I think this should get you close:

  WHERE ColumnName + '.' LIKE '%[^0-9]1 % 202[^0-9]%'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".