Results 1 to 9 of 9
  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: SQL equivalent decode function - issues

    I am a novice in SQL server programming.

    I was trying to write a string search and replace function. Basically what I want is a function that inputs the column_name (@input_field), search pattern (@srch_str) and a replace string (@rep_str) so that if this function is used with a column of a table, if the column data matches the search pattern replace with the replace string or else do nothing. Here is the function I wrote.

    Code:
    USE [MyTestDB] 
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE function [dbo].[TMP_DECODESQL]
    (
    	@input_field varchar(100),
    	@srch_str varchar(100),
    	@rep_str varchar(100)
    )
    
    returns varchar
    
    as
    
    begin
    	declare @ret_str varchar(100)
    
    	if @input_field = @srch_str
    		set @ret_str = @rep_str
    	else
    		set @ret_str = @input_field
        
        return @ret_str 
    end
    When I use this function in the sql query like below:
    Code:
    select 
    CustID,
    dbo.TMP_DECODESQL(CustID, '', 'xyz'),
    Address,
    dbo.TMP_DECODESQL(Address,'','xyz')
    from DCustomer where UnID = '269949'
    I get
    CustID------(No column name)------Address--------(No column name)
    67170-----------6--------------------- ----------------x

    Looks like the replace string is only picking the first character of the replace string.

    I know we can use a case statement but I am trying to use this function in the "where clause" with complex condition making it very difficult to use the case statment.

    Any ideas or suggestions would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    USE [MyTestDB] 
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE function [dbo].[TMP_DECODESQL]
    (
    	@input_field varchar(100),
    	@srch_str varchar(100),
    	@rep_str varchar(100)
    )
    
    returns varchar(100)
    
    as
    
    begin
    	declare @ret_str varchar(100)
    
    	if @input_field = @srch_str
    		set @ret_str = @rep_str
    	else
    		set @ret_str = @input_field
        
        return @ret_str 
    end
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2011
    Posts
    7
    Thank you much.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Does this function do anything else than the standard REPLACE function?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    May 2011
    Posts
    7
    One thing I found is that REPLACE searches and replaces strings one character at a time.

    For example

    Select
    serial_no,
    REPLACE(name, 'dav', '###') replace_col,
    name
    from mytable
    where snumber in (1, 2)



    serial_no.....replace_col............name
    ..1..............###id...............david
    ..2..............###.................dav

    That's what i donot want
    What I more wanted is more like:

    serial_no.....replace_col............name
    ..1..............david...............david
    ..2..............###.................dav

    The function worked for the cases like these, and well - but it didn't quite resolve my overall problem :-(

    Thanks

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is it possible that you made a mistake in your first post?
    Code:
    select 
    CustID,
    dbo.TMP_DECODESQL(CustID, '', 'xyz'),
    Address,
    dbo.TMP_DECODESQL(Address,'','xyz')
    from DCustomer where UnID = '269949'
    should give this result
    Code:
    CustID------(No column name)------Address--------(No column name)
    67170-----------6--------------------- ----------------x
    Shouldn't it have been something in the line of
    Code:
    select 
    CustID,
    dbo.TMP_DECODESQL(CustID, '67170', '6'),
    Address,
    dbo.TMP_DECODESQL(Address,'','xyz')
    from DCustomer where UnID = '269949'
    Does the fact that you only see an 'x' where you would expect an 'xyz' has got anything to do with the display width of your GUI tool?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The original returns just varchar, which is understood to mean varchar(1).

  8. #8
    Join Date
    May 2011
    Posts
    7
    Correct. And after doing -

    Code:
    returns varchar(100)
    as Pat suggested, it resolve the issue.

  9. #9
    Join Date
    May 2011
    Posts
    7
    [QUOTE=Wim;6502357]Is it possible that you made a mistake in your first post?[CODE]

    I was actually defining my issues using the query syntax and the result.For saying "Returns Varchar" in the function without specifying the length, it was just keeping 1 character and chopping off the rest.

Posting Permissions

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