locked
Is there a function like SUBSTRING but for integers? RRS feed

  • Question

  • I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers.  Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
    Friday, April 14, 2006 5:18 AM

Answers

  • this left function is for string. if you pass in a integer, the integer value is implicit convert to string
    so 20010112 is converted to '20010112'.

    You can convert the integer to string before using substring

    substring(convert(varchar(10), @intvar), 5, 2)
    Friday, April 14, 2006 11:10 AM
  • You use LEFT function.

    SELECT LEFT(20010112, 4)

    You should get 2001

    This function works on string too.

    Friday, April 14, 2006 5:58 AM

All replies

  • You use LEFT function.

    SELECT LEFT(20010112, 4)

    You should get 2001

    This function works on string too.

    Friday, April 14, 2006 5:58 AM
  • this left function is for string. if you pass in a integer, the integer value is implicit convert to string
    so 20010112 is converted to '20010112'.

    You can convert the integer to string before using substring

    substring(convert(varchar(10), @intvar), 5, 2)
    Friday, April 14, 2006 11:10 AM
  • There might also be situations in which you can simply apply integer arithmetic.  The string functions might be more appropriate for this particular example; nonetheless, here is an example of how you might be able to do this with mere arithmetic:

    select 
     20010112/10000 Left_4,
     20110112 % 10000 Right_4
    /* -------- Output: --------
    Left_4   Right_4
    ----------- -----------
    2001    112
    
    <br/><br/>*/<br/><br/> <br/><br/><br/>
    
    

    .

    Tuesday, August 2, 2011 8:01 PM
  • Thank you, this helps me a lot!

    Thursday, June 23, 2016 6:13 PM