Need help to finding pattern

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  June 12, 2014 at 6:11 am

  #396902

  Hi There,

  I need to find the pattern like following ...

  I have one column named Solution, In that user typed information will be captured. Some where in that column user entering his id. Id will be like ,

  John123456

  Kenn456123

  Pattern : first 4 digit will be alphabets , and next 6 digits will be numeric

  So ID can be any where in the string. Based on the pattern I need to extract the id.

  I want achieve like ,

  %[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%

  Thanks in advance

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 12, 2014 at 7:00 am

  #1721051

  DwainC wrote this article [/url]a couple of years ago which might offer you some ideas.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 12, 2014 at 8:35 am

  #1721091

  I missed the question. What's your problem?

  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: 101101

  More actions

  June 12, 2014 at 9:42 am

  #1721133

  SUBSTRING(sample_text, PATINDEX('%[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%', sample_text), 10)

  For example:

  SELECT

  sample_text,

  SUBSTRING(sample_text, PATINDEX('%[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%', sample_text), 10)

  FROM (

  SELECT 'some other 44 numbers and text 555-555-1212 for John123456 and whatever else' AS sample_text

  UNION ALL

  SELECT '89qa90w8 Kenn456123 asl 89-7n asdlka aopa wsoop s sop s s'

  ) AS sample_data

  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".