Post reply

Extracting the Integer

  • December 20, 2002 at 3:28 am

    #58657

    Can Anyone Help?

    I have a range of alphanumeric product codes

    for example HBHORT023, HB894, H163A and I wish to extract the integer portion of these codes i.e 023, 894, 163. I know that I cannot use the INT() Function, is there any other way to acheive this in SQL.

  • December 20, 2002 at 3:43 am

    #444912

    Hi, try this: -

    declare @AlphaStr varchar(10)

    , @RtnStr varchar(10)

    , @i int

    select @i = 1, @RtnStr = '',@AlphaStr = 'a2c123'

    while @i <= len(@AlphaStr)

    begin

    if ascii(substring (@AlphaStr, @i,1)) between 48 and 57

    begin

    select @RtnStr = @RtnStr + substring (@AlphaStr, @i,1)

    end

    select @i = @i + 1

    end

    select @RtnStr

    Regards,

    Andy Jones

    Edited by - andyj93 on 12/20/2002 03:44:11 AM

    Edited by - andyj93 on 12/20/2002 03:44:42 AM

    .

  • December 20, 2002 at 4:03 am

    #444913

    Thanks Andy, but how do I use this against all the product codes in the column in my Table (SQL6.5)

  • December 20, 2002 at 4:23 am

    #444914

    Hi, you could incooperate the logic above in a UDF, although in 6.5 not sure??

    Regards,

    Andy Jones

    .

  • December 20, 2002 at 5:12 am

    #444915

    Can't do UDF's in 6.5. Could I do this in a stored procedure using a cursor maybe? and if so how do i do it?

    Thanks in advance

    Pete

  • December 20, 2002 at 5:20 am

    #444916

    Possible, but performance would be poor on a large table. You could maybe trigger the integer part into another column when inserting / updating or simply insert / update into another column if your data access is via stored procedures. These suggestions are no good of course if you are simply reporting on an existing database and have no control over the inserts.

    Regards,

    Andy Jones

    .

  • December 20, 2002 at 5:55 am

    #444918

    Or you can shorten a little by changing the while loop to this

    while @i <= len(@AlphaStr)

    select @RtnStr = @RtnStr + CASE WHEN substring (@AlphaStr, @i,1) LIKE '[0-9]' THEN substring (@AlphaStr, @i,1) ELSE '' END, @i = @i + 1

    Because you have no outputs you can increment the value inside the select.

    Also, if you need this in a SELECT statement and are using SQL 2000 you can make the process a function so you do not have to create a cursor.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply