Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Unanswered: SELECT only number from CHAR fld

    Hi all!

    I am looking for a way to select only numbers OR only the first word from a field.

    EG:
    1 cartoon => 1
    2 cartoons => 2
    200 cartoons => 200
    7 Allo => 7
    1 Poil 2 Pirates 3 Bizounes => 1
    25Kilos2 => 25

    Where the values after the => is what I want as a result.

    Anybody knows how to do that ?
    Last edited by ortho; 02-25-08 at 17:33.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    So basically it starts from the left, and as soon as it find a character not between 0-9 it stops.

    ...

    This is what I got yet... but it doesn't works.

    SELECT CAST(STUFF(USERDEF1, CHARINDEX(' ', USERDEF1), LEN(USERDEF1) - CHARINDEX(' ', USERDEF1) - 1, '') AS INT) AS USERDEF1 FROM SOP10106

    Well it works fine for any values like this :
    1 2 3 Pouette => 1
    12 Allo => 12

    But it doesn't works for this kind of values :
    1LLL => ERROR CONVERTING (1LLL AS INT)
    007JAMES BOND => ERROR CONVERTING (007JAMES AS INT)
    JAJA 1 => ERROR **

    ** This should return 0
    Last edited by ortho; 02-25-08 at 17:40.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  3. #3
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    If I could replace any non-numeric characters (but spaces CHR(32)), it will do the thing... But how is it possible without using regexp?


    Okay, I look like a crazy person talking to myself so I'll wait for your reply(ies).

    Btw I'm using MS SQL SERVER 2005 SP2
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    IF Object_Id('dbo.fCharFilter', 'FN') IS NOT NULL
       DROP FUNCTION dbo.fCharFilter
    GO
    --  ptp  20080225  See http://www.dbforums.com/showthread.php?t=1627718
    
    CREATE FUNCTION dbo.fCharFilter (
       @cGoodChars		NVARCHAR(20)		-- These are the "good" chars to keep
    ,  @cOriginal		NVARCHAR(255)		-- This is the "original" image
    ) RETURNS NVARCHAR(255) AS
    BEGIN
       DECLARE @i		INT			-- Iteration index
    ,     @cResult		NVARCHAR(255)		-- 88 level (anybody remember that reference?)
    
       SET @cResult = ''				-- Set to empty string for now
       SET @i = DataLength(@cOriginal) / 2		-- Use DataLength to allow for trailing spaces
    
       WHILE 1 <= @i				-- Process C-wise
          BEGIN
             IF 0 < CharIndex(SubString(@cOriginal, @i, 1), @cGoodChars)
                SET @cResult = SubString(@cOriginal, @i, 1) + @cResult
    
             SET @i = @i - 1
          END
    
       RETURN CASE WHEN '' = @cResult THEN NULL ELSE @cResult END
    END
    GO
    
    SELECT       0, dbo.fCharFilter('0123456789', 'Now is the time')
    UNION SELECT 1, dbo.fCharFilter('0123456789', '1Now is the time')
    UNION SELECT 2, dbo.fCharFilter('0123456789', 'Now is 2 the time')
    UNION SELECT 3, dbo.fCharFilter('0123456789', 'Now is the time3')
    UNION SELECT 4, dbo.fCharFilter('0123456789', '4Now 5is 6the 7time8')
    -PatP

  5. #5
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Wow great!

    Thanks Pat, I have just few modifications to do but it works!

    Less is more.
    How long is now?
    http://www.lesouterrain.com

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Pat Phelan
    [ DECLARE @i INT -- Iteration index
    , @cResult NVARCHAR(255) -- 88 level (anybody remember that reference?)
    Ah yes ... the old COBOL 88 level

    Q What is level 88?

    A The level 88 in the Data Division can be used to give condition names to the values that a field contain. When this level is specified you can use the condition name instead of is = to in the IF statement. Condition name should be specified under Level 88 immediately following the field description.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    omg omg omg i had totally forgotten about 88s

    remember 77s too?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by r937
    omg omg omg i had totally forgotten about 88s

    remember 77s too?
    Of course ... COBOL was the next language I learned after BASIC.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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