Search for specific data

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  April 21, 2008 at 3:22 pm

  #189304

  I have a column with data as

  00070111

  0001111

  00000111

  00040111

  00044111

  000001111

  I want a query which would get me all data preceding with 0's and ending as 1111. does anyone have a script handy to make such a search. The result should be

  0001111

  00000111

  000001111

  TIA

• sqltung

  Hall of Fame

  Points: 3962

  More actions

  April 21, 2008 at 4:11 pm

  #805233

  ishaan99 (4/21/2008)

  ---

  I have a column with data as

  00070111

  0001111

  00000111

  00040111

  00044111

  000001111

  I want a query which would get me all data preceding with 0's and ending as 1111. does anyone have a script handy to make such a search. The result should be

  0001111

  00000111

  000001111

  TIA

  It would be helpful to know the maximum length of the string. Also, if the results should end with '1111', then '00000111' should not be in the result as you stated.

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  April 21, 2008 at 4:56 pm

  #805239

  Try this:

  create table #xx (z1 varchar(10))

  insert into #xx

  select '00070111' union all

  select '0001111' union all

  select '00000111' union all

  select '00040111' union all

  select '00044111' union all

  select '000001111'

  select * from #xx

  where convert(int, z1) = 1111

  and z1 like '%1111'

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 22, 2008 at 1:07 pm

  #805708

  ksullivan (4/21/2008)

  ---

  Try this:

  create table #xx (z1 varchar(10))

  insert into #xx

  select '00070111' union all

  select '0001111' union all

  select '00000111' union all

  select '00040111' union all

  select '00044111' union all

  select '000001111'

  select * from #xx

  where convert(int, z1) = 1111

  and z1 like '%1111'

  Unfortunately, this will include values like '001001111' and '1111111', which if I understand the OP's request, should be excluded.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 22, 2008 at 1:12 pm

  #805712

  ishaan99 (4/21/2008)

  ---

  I have a column with data as

  00070111

  0001111

  00000111

  00040111

  00044111

  000001111

  I want a query which would get me all data preceding with 0's and ending as 1111. does anyone have a script handy to make such a search. The result should be

  0001111

  00000111

  000001111

  TIA

  Try this:

  Select NumColumn From Table1

  Where NumColumn Like '0%1111'

  And NOT (NumColumn Like '%10%')

  And NOT (NumColumn Like '%11111')

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• ksullivan

  SSCrazy

  Points: 2346

  More actions

  April 22, 2008 at 1:37 pm

  #805724

  select * from #xx

  where convert(int, z1) = 1111

  and z1 like '%1111'

  will not include values like '001001111' and '1111111' because both convert to ints greater than 1111.

  Since ishaan99 did not say if his column can have only 4 characters one of rbarryyoung's LIKE clauses is prudent

  select * from #xx

  where convert(int, z1) = 1111

  and z1 like '%1111'

  and z1 Like '0%1111'

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 22, 2008 at 1:44 pm

  #805731

  and z1 like '%1111'

  and z1 Like '0%1111'

  I think these two are redundant. Just use the second one, and it will handle the first one.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  April 22, 2008 at 4:20 pm

  #805812

  ksullivan (4/22/2008)

  ---

  select * from #xx

  where convert(int, z1) = 1111

  and z1 like '%1111'

  will not include values like '001001111' and '1111111' because both convert to ints greater than 1111.

  Right you are, my bad.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]