database column value check

• Desh Gupta

  SSC Journeyman

  Points: 76

  More actions

  November 14, 2005 at 5:43 am

  #102927

  hi

  please help me how can i check a perticular column value weather it is number, char or alpha numeric in select statement

   

  thanks & regards

  deshbandhu

   

   

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  November 14, 2005 at 6:08 am

  #603626

  If you want to check for a specific position, then you can use "substring" otherwise you could try something like this - comment out the 'Hullo' first and then '12345' to see the 2 different print messages:

  declare @VarChar VarChar(5)
  set @VarChar = 'Hullo'
  --set @VarChar = '12345'
  
  if isnumeric(@VarChar) = 1
  print 'it is a number'
  else
  print 'it is not a number'
  

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

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  November 14, 2005 at 6:19 am

  #603628

  ..or use LIKE / NOT LIKE with wildcards if ISNUMERIC() doesn't cut it.

  Using wildcards will also provide some control that ISNUMERIC() doesn't give you, should it be needed.

  BOL has a nice section on 'LIKE' keyword showing some techniques with wildcards and patternmatching.

  /Kenneth

• Jeff Moden

  SSC Guru

  Points: 1004642

  More actions

  November 15, 2005 at 9:55 pm

  #604086

  As Kenneth suggested, ISNUMERIC may not cut it.  It allows for seemingly non-numeric characters such as "d", "e", and "." so that it will support scientific and exponential notation.  Continuing Sushila's fine example...

  DECLARE @TestString VARCHAR(100)

      SET @TestString = '12345' --<< Change this to test...

   SELECT CASE

              WHEN @TestString NOT LIKE '%[^0-9]%' THEN 'All Numeric'

              WHEN @TestString NOT LIKE '%[0-9]%'  THEN 'All Alpha'

              ELSE 'Mixed'

          END

  Again, as Kenneth suggested, it depends on what you want... you could allow the decimal point and other characters depending on what your definition of a trully numeric value might be.  Most people mean "All Digits" when they say "Is Numeric".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)