Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: using a function

    I have found this very practicle function

    CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000),
    @FROM VARCHAR(256), @TO VARCHAR(256))
    RETURNS VARCHAR (8000)
    AS
    BEGIN
    -- effets de bord
    IF @VALIN IS NULL
    RETURN NULL
    IF @FROM IS NULL OR @TO IS NULL
    RETURN NULL
    IF LEN(@VALIN) = 0
    RETURN @VALIN
    -- initialisation
    DECLARE @I INTEGER
    DECLARE @OUT VARCHAR(8000)
    SET @OUT = ''
    -- lecture caractre par caractre
    SET @I =1
    WHILE @I <= LEN(@VALIN)
    BEGIN
    IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0
    BEGIN
    IF LEN(@TO) >= PATINDEX('%'
    + SUBSTRING(@VALIN, @I, 1)
    + '%', @FROM)
    SET @OUT = @OUT
    + SUBSTRING(@TO, PATINDEX('%'
    + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1)
    END
    ELSE
    SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1)
    SET @I = @I + 1
    END
    RETURN @OUT
    END


    but how can I use it in MS SQL 2000-25000 ?

    FN_TRANSLATE(dbo.Users.Name, '',
    'aaaceeeeiioouuuy')

    doesnt work

    I get an error on the colum name dbo.Users.Name


    thank you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you call a user-defined function, you must specify the owner of the function. Make sure you are calling it like this:

    select dbo.FN_TRANSLATE(dbo.Users.Name, '', 'aaaceeeeiioouuuy')

    ...and make sure Users.Name is actually owned by dbo.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    select dbo.FN_TRANSLATE(dbo.Users.Name, '&#224;&#226;&#228;&#231;&#233;&#232;&#234;&#235;& #238;&#239;&#244;&#246;&#249;&#251;&#252;&#255;', 'aaaceeeeiioouuuy') FROM dbo.Users

    works fine

    thank you

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What the hell does that do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    it changes '&#224;&#226;&#228;&#231;&#233;&#232;&#234;&#235;& #238;&#239;&#244;&#246;&#249;&#251;&#252;&#255;' into 'aaaceeeeiioouuuy'

    any one of the left char with the corresponding one on the right (same position)

    it works really fine

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It performs the same service as a function that is build into Oracle.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    I found it on a website, very practicle

Posting Permissions

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