Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Talking Unanswered: 'nother integer question for a number of you

    "number of you"??? Integer???? get it??? OK...it's early/late/whenever...

    without the search function, I am unable to easily find what I am nearly sure is an easy one...

    but...I am converting a CSV string to a table of integers using a stolen fn_CsvToInt function that returns a table of integers...found THAT on here earlier, b'gawd

    In any case, I am using
    Code:
    IF (ISNUMERIC(yada) = 1)
    to validate that the sub-string of the input CSV list of number is, in fact, a number...but what I am after is INTEGERS only...so if someone sends in some stupid stuff (though I am SURE none of MY users would do such a thing ) like:
    Code:
    select * from fn_CsvToInt('1,43,5.7,byte_me,100)
    what I want to return is 1, 43, 100 (ignoring the 5.7 and 'byte_me' entries in the list). However, using the ISNUMERIC allows the sneaky '5.7' entry past, and then I get an error trying to convert it to an integer later.

    Any easy way to check for integers? Or do I need to whip out a quick fn_IsInteger UDF for my evil and exclusionary purposes?

    Thanks!
    Paul
    Last edited by TallCowboy0614; 06-04-04 at 16:26. Reason: nonya
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Nevermind...found it...

    Patern matching...go figure...
    Code:
    	  IF ((ISNUMERIC(@Csv_value) = 1) AND (PATINDEX( '%[^0-9]%', @Csv_value) = 0))
    		Insert @IntTable Values (Cast(@Csv_value as int))
    or...even better...
    Code:
    	  IF (PATINDEX( '%[^0-9]%', @Csv_value) = 0)
    		Insert @IntTable Values (Cast(@Csv_value as int))
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Now what fun is this if you're going to answer all your own questions?

    Good way to get the post count up though....

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not only that, but negative numbers will add some glee to his day when they hit the pattern match!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Thumbs up

    Quote Originally Posted by Pat Phelan
    Not only that, but negative numbers will add some glee to his day when they hit the pattern match!

    -PatP
    I'm always up for glee...in fact, I think I'll start a Glee Club...oh wait...it's been done

    Thanks for the heads up...but fortunately, in my happy little world, negative integers are imaginary numbers (take THAT, mister Descartes!!!)

    Bwahahahahaha...No foolish users can damage my world!!! (famous last words of...well...just about EVERYONE!)

    Yeah, Brett, sorry...I just had to answer myself 'cause it's 11 days to payday, and I couldn't afford the beer for another answer from you
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Brett works for beer now ??? I thought he required at least a pitcher of Margaritas before he'd even start working on a problem!

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Ahhhhh...margaritas and SQL...a match made in heaven...*sigh*

    well, for that matter...margaritas and...well...ANYTHING!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Wouldn't this be simpler?:

    if ISNUMERIC(replace(yada, '.', 'Z')) = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    When did you start smoking crack?

    Code:
    DECLARE @x decimal(15,4)
    SELECT @x = 15.4
    SELECT @x
    SELECT ISNUMERIC(replace(@x, '.', 'Z'))
    
    SELECT ISNUMERIC(replace(@x, '.', ''))
    
    SELECT @x = -15.4
    SELECT @x
    
    SELECT ISNUMERIC(replace(@x, '.', ''))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Isn't this the behavior he wants?

    SELECT ISNUMERIC(replace('15', '.', 'Z')) --Integer, so accept it
    SELECT ISNUMERIC(replace('-15', '.', 'Z')) --Negative integer, so accept it
    SELECT ISNUMERIC(replace('15.4', '.', 'Z')) --Not an integer, so reject it.
    SELECT ISNUMERIC(replace('Wheres my crack?', '.', 'Z')) --Not an integer, so seek methadone treatement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yep, that's essentially the behavior I'm after (though, of course I'd prefer a buxom blonde in leather and handcuffs...but that's a behavior for a different board *ahem*

    Not sure if that's really much "simpler" though - maybe one is easier on resources than the other? Thanks for another viewpoint though (really!!!) I gotta do better at combining functions in my code.

    Regardless...I guess it just depends on who's reading the code. I kinda like the pedanticism (look ma, he made a new word!) that tends to surround the use of regular expressions though

    Thanks again for expending brain cells on my behalf!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •