allow varchar value to string and decimal parts

  • i need a query that make

    'A010' + 1 = 'A011' or

    'ABC13456' + 12 = 'ABC13468'

    how can i do this

  • Dear Friend

    Following procdure will help you to get your result.

    Create Procedure ADD_NUMBER_TO_STRING

    (

    @Value varchar(20),

    @AddNum int

    )

    AS

    --===============================

    --Question Is Described As below

    --i need a query that make

    --'A010' + 1 = 'A011' or

    'ABC13456' + 12 = 'ABC13468'

    --==================================

    --Declare variables to be used

    Declare @NumPart int

    Declare @Counter int

    Declare @Length int

    Declare @Var as varchar(1)

    Declare @NumStart int

    --Create Temperory Table

    Create table Tbl_Temp(AlphaBet varchar(1))

    --Insert Alphabets and 0 for number start with 0

    Insert Into Tbl_Temp values('0')

    Insert Into Tbl_Temp values('A')

    Insert Into Tbl_Temp values('B')

    Insert Into Tbl_Temp values('C')

    Insert Into Tbl_Temp values('D')

    Insert Into Tbl_Temp values('E')

    Insert Into Tbl_Temp values('F')

    Insert Into Tbl_Temp values('G')

    Insert Into Tbl_Temp values('H')

    Insert Into Tbl_Temp values('I')

    Insert Into Tbl_Temp values('J')

    Insert Into Tbl_Temp values('K')

    Insert Into Tbl_Temp values('L')

    Insert Into Tbl_Temp values('M')

    Insert Into Tbl_Temp values('N')

    Insert Into Tbl_Temp values('O')

    Insert Into Tbl_Temp values('P')

    Insert Into Tbl_Temp values('Q')

    Insert Into Tbl_Temp values('R')

    Insert Into Tbl_Temp values('S')

    Insert Into Tbl_Temp values('T')

    Insert Into Tbl_Temp values('U')

    Insert Into Tbl_Temp values('V')

    Insert Into Tbl_Temp values('W')

    Insert Into Tbl_Temp values('X')

    Insert Into Tbl_Temp values('Y')

    Insert Into Tbl_Temp values('Z')

    --

    Set @NumStart = 0

    Set @Counter = 1

    --Set @AlphaPart = substring(@Value,1,2)

    --Set @NumPart = Substring(@Value,2,3)

    --Seth Length of string

    Set @Length = len(@Value)

    while (@Counter <= @Length)

    Begin

    Set @Var = Substring(@Value, @Counter, @Counter + 1)

    if( @Var = (Select Alphabet from Tbl_Temp where Alphabet = @Var))

    Begin

    Set @Counter = @Counter + 1

    End

    Else

    Begin

    Set @NumStart = @Counter

    Break;

    End

    End

    Set @NumPart = Substring(@Value, @NumStart ,@Length)

    Set @Value = Substring(@Value,1,@NumStart - 1)

    Set @NumPart = @NumPart + @AddNum

    Set @Value = @Value + cast(@NumPart as varchar(20))

    --Print Required result

    Select 'Final Value : '+ @Value

    --Drop Temperory Table

    Drop Table Tbl_Temp

    --=================

    --Test : Execute Following Just Select Following Line and Execute

    --Exec ADD_NUMBER_TO_STRING 'A010',1

    --Exec ADD_NUMBER_TO_STRING 'ABC13456',12

    Cheers,

    Saurabh Singh

  • Thats good saurabh.k.singh.

    May I suggest that if you changed the declaration @NumPart from int to float, and @AddNum from int to float, then you will be able to add decimal figures as well :D.

    EDIT:

    Actually, that only allows one decimal place at the moment.

  • thank's for help saurabh.k.singh

    you are great

  • Hi there,

    I have another solution. But this one also accepts symbols or any other characters EXCEPT numbers.

    I'm not saying the other post was wrong. Actally I really like the post, very helpful and you can also use that sproc to do the same as mine by inputing numbers into the tables and making it do the opposite when filtering.

    Oh yeah, i forgot to make this into decimal type but if you make it decimal, I think it will work fine... except for the last IF STATEMENT. But I think you can already alter it yourself.

    Hope this helps

    CREATE PROC AddToCodeNumber

    @codeVARCHAR(50),

    @numberINT

    AS

    BEGIN

    DECLARE @LengthINT,

    @iINT

    SELECT@Length = len(@code),

    @i = 0

    WHILE (SELECT SUBSTRING(@code,@length-@i,1)) IN ('1','2','3','4','5','6','7','8','9','0')

    BEGIN

    SELECT @i=@i+1

    END

    SELECT @number = RIGHT(@code,@i) + @number

    IF LEN(@number)=LEN(RIGHT(@code,@i))

    BEGIN

    SELECT LEFT(@code,@Length-@i) + CAST(@number AS VARCHAR(MAX))

    END

    ELSE

    BEGIN

    SELECT 'ERROR'

    END

    END

    RETURN

    GO

    EXEC AddToCodeNumber

    @code='ABC/*-+32100',

    @number=212

    EXEC AddToCodeNumber

    @code='ABC/*-+32100',

    @number=2122346

    Tell me if this was helpful ^__^ Thanks!

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • Try this version using a Tally table. No loops, no cursors.

    IF OBJECT_ID ( 'tempdb..#Tally' ) IS NOT NULL

    BEGIN

    DROP TABLE #Tally

    END

    GO

    DECLARE @AlphaNumber varchar ( 20 )

    DEcLARE @numtoadd decimal ( 20, 4 )

    DECLARE @NumPart decimal ( 20, 4 )

    DECLARE @Result varchar ( 20 )

    DECLARE @strlen int

    DECLARE @numpos int

    SET @AlphaNumber = 'ABC12345.6789'

    SET @numtoadd = 0.3211

    SET @strlen = DATALENGTH ( @AlphaNumber )

    SELECT TOP ( @strlen )

    IDENTITY ( int, 1, 1 ) AS N

    INTO #Tally

    FROM master.dbo.syscolumns

    ALTER TABLE #Tally

    ADD C char ( 1 )

    UPDATE #Tally SET C = SUBSTRING ( @AlphaNumber, N, 1 )

    --SELECT * FROM #Tally

    SELECT @numpos = MIN ( N ) FROM #Tally WHERE C BETWEEN '0' AND '9' OR C = '.'

    --PRINT @numpos

    SET @NumPart = CAST ( SUBSTRING ( @AlphaNumber, @numpos, @strlen ) AS decimal ( 20, 4 ) )

    --PRINT @NumPart

    SET @NumPart = @NumPart + @numtoadd

    SET @Result = LEFT ( @AlphaNumber, @numpos - 1 ) + LTRIM ( STR ( @NumPart, @strlen, 4 ) )

    PRINT @AlphaNumber + ' + ' + LTRIM ( STR ( @numtoadd, 10, 4 ) ) + ' = ' + @Result

    DROP TABLE #Tally

    I know, it breaks if there is no numeric part or there is more than one decimal point 🙂

    Regards,

    Jan

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Hi,

    try the below one.

    DECLARE @Text VARCHAR(16)

    SET @Text = 'ABC222'

    SELECT SUBSTRING(@Text,PATINDEX('%[0-9]%',@Text),LEN(@Text)) + 100

    ---

  • Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.

    create table #tester

    (seq tinyint null,

    txt varchar(20) null,

    num integer null)

    set nocount on

    insert #tester values (1,'ABC44',10)

    insert #tester values (2,'ABC44',0)

    insert #tester values (3,'',10)

    insert #tester values (4,'ABC4',10)

    insert #tester values (5,'ABC44',1000)

    insert #tester values (6,'ABC',10)

    insert #tester values (7,null,10)

    insert #tester values (8,'ABC',null)

    insert #tester values (9,null,null)

    set nocount off

    select seq,

    case when isnull(txt,'') = '' then isnull(convert(varchar,num),'')

    when patindex('%[0-9]%',txt) = 0 then txt + isnull(convert(varchar,num),'')

    else substring(txt,1,patindex('%[0-9]%',txt) - 1) +

    convert(varchar,convert(int,substring(txt, patindex('%[0-9]%',txt),len(txt)) + isnull(num,0)))

    end as 'result'

    from #tester

    order by seq

    drop table #tester

    /*

    seq result

    ---- --------------------------------------------------

    1 ABC54

    2 ABC44

    3 10

    4 ABC14

    5 ABC1044

    6 ABC10

    7 10

    8 ABC

    9

    (9 row(s) affected)

    */

  • sqluser (8/29/2008)


    Hi,

    try the below one.

    DECLARE @Text VARCHAR(16)

    SET @Text = 'ABC222'

    SELECT SUBSTRING(@Text,PATINDEX('%[0-9]%',@Text),LEN(@Text)) + 100

    ---

    Doesn't return the "ABC" part... 🙂

    --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)

  • Mike Mullen (8/29/2008)


    Here is another solution that builds on the previous suggestions. This one handles a few more variations of the text data and the number to be added.

    Yes, it does. Nicely done! 🙂

    --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)

  • Just a thought... why not keep the alpha and numeric portions separate and combine them only in a calculated column??? Then you wouldn't have to mess with all of this. 😉

    --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)

Viewing 11 posts - 1 through 10 (of 10 total)

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