SQL select statement help

• najeeb ahmad

  Newbie

  Points: 5

  More actions

  April 25, 2006 at 1:44 pm

  #68791

  Hi,

  I have column with alphanumberic fields like:

  trader1.dbpt

  1234.mcxl

  abc243.cexl

  234-lab.cxcl

  There are following characteristics of this data:

  -fifth last place is always decimal

  -always four characters after the decimal

  What I want:

  -only those rows which have ".mcxl" at the end

  -rows should NOT have numbers in them

  -rows should not have "-" in them

  -want to sort according to the length, shortest should come first

  Please guide how to accomplish all this on MS Access in SQL statement

  thanks for the help,

  Silverspring

• jfmccabe

  SSCrazy

  Points: 2547

  More actions

  April 26, 2006 at 3:46 am

  #634235

  Create an mdb file. Link your table. Create queries as you want.

  .mcxl at end is : Create a column Right([fieldname],5) with criteria ".mcxl "

  finding - is : Create a column Instr([fieldname],"-") with criteria  0

  sort by length is : Create a column  LEN([Fieldname]) and sort ascending

  no numbers : need a bit of code. Create a module with a function

  Public Function fnNumeric(Special) as Boolean

  Dim a as Integer, b as integer

  fnNumeric = False

  a = len(Special)

  for b =1 to a

  if isnumeric(Mid(Special,a,1)) Then

  fnNumeric = True

  exit for

  next

  End Function

  In the query create a column fnNumeric([Special]) with criteria True.

  Most of this can be done in T SQL using most of the same functions. Instr is replaced by CharIndex and you can do the function  using IN(0,1,2,3,4,5,6,7,8,9) instead of IsNumeric.

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  April 26, 2006 at 8:14 am

  #634301

  this is completely untried & untested so you need to "play around with it"...

  select * from myTable where right(name, 4) = 'mcxl' and col like replicate('[^0-9-]', datalength(col) - charindex(col, '.'))
  

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• Charles Wannall

  Ten Centuries

  Points: 1023

  More actions

  April 26, 2006 at 9:11 am

  #634340

  Used the table of data shown below, in order to provide some rows that pass the tests:

  trader1.dbpt

  1234.mcxl

  abc243.cexl

  234-lab.cxcl

  shouldpass.mcxl

  shouldalsopass.mcxl

  shouldpasstoo.mcxl shouldnot-pass.mcxl

  This SQL

  --only those rows which have ".mcxl" at the end

  --rows should NOT have numbers in them

  --rows should not have "-" in them

  --want to sort according to the length, shortest should come first

  SELECT [MyField], len(MyField) as Length

  FROM [AlphaNumericData]

  where MyField Like '*.mcxl'

  and MyField Not like '*[0-9,-]*'

  Order by Len(MyField)

  Produces this result

  MyField	Length
  shouldpass.mcxl	15
  shouldpasstoo.mcxl	18
  shouldalsopass.mcxl	19

• jfmccabe

  SSCrazy

  Points: 2547

  More actions

  April 26, 2006 at 9:17 am

  #634343

  Very neat!

  Nice trick with "*[0-9,-]*"!