string manipulation in sql server 2000

  • i have a function that converts a string. here's the example:

    string Manual Preimpresion Digital By Pasarisa, Gabriel will be converted to Manual Preimpresion Digital By Gabriel Pasarisa

    string Dolli, Cocina Para Todos By La, Nacion Sa- Dolli Irigoyen/ Irigoyen, Dolly will be converted to Cocina Dolli Para Todos By La, Nacion Sa- Dolli Irigoyen/ Irigoyen, Dolly

    As you can see the 2nd string afffected as well, but it's not properly.

    Basically function find a string with a comma, remove the comma and put FirstName first and then LastName, i.e. reverse them. The problem is the function will affect strings containt more than just one comma as well and that's not what I want to. I want to convert strings contain one comma only. There are other strings that don't have any comma or some other characters, like +, -, *, :, etc..., so function disregards them.

    here's the function:

    CREATE FUNCTION fnc_ReverseNameInString (

        @string VARCHAR(1000)

    )

    RETURNS VARCHAR(1000)

    /*

    this function is used to parse a string that contains a comma, i.e.

    it will convert "Manual Preimpresion Digital By Pasarisa, Gabriel" string to

    "Manual Preimpresion Digital By Gabriel Pasarisa" by removing comma and reverse

    Last Name with First Name. It will not parse strings that don't containt commas

    or have more than one comma

    USAGE: select dbo.ReverseNameInString(title) from TitleIngram

    */

    AS

    BEGIN

    IF CHARINDEX(',', @string) = 0

     RETURN @string

    DECLARE @comma int

    DECLARE @startOfLastName int

    DECLARE @startOfFirstName int

    DECLARE @lengthOfFirstName int

    SET @comma = CHARINDEX(',', @string)

    SET @startOfLastName = @comma - CHARINDEX(' ', REVERSE(LEFT(@string, @comma - 1))) + 1

    IF @startOfLastName > @comma

     SET @startOfLastName = 1

     SET @startOfFirstName = @comma + PATINDEX('%[^ ]%', SUBSTRING(@string, @comma + 1, 1000))

     SET @lengthOfFirstName = CHARINDEX(' ', LTRIM(SUBSTRING(@string, @comma + 1, 1000))) - 1

    IF @lengthOfFirstName <= 0

         SET @lengthOfFirstName = LEN(@string) - @comma   

     RETURN  LEFT(@string, @startOfLastName - 1) +

          + SUBSTRING(@string, @startOfFirstName, @lengthOfFirstName) + ' '

          + SUBSTRING(@string, @startOfLastName, @comma - @startOfLastName) + ' ' +

          + SUBSTRING(@string, @startOfFirstName + @lengthOfFirstName + 1, 1000)

    END

     

    any help appreciated.

    Thanks,

    Albert.

  • Replace this set of lines:

    IF CHARINDEX(',', @string) = 0

     RETURN @string

    with

    IF (CHARINDEX(',', @string) = 0) OR (PATINDEX('%,%,%', @string) > 0)

     RETURN @string

     

    K. Brian Kelley
    @kbriankelley

  • Brian,

     

    thank you very much for your help.

    albert.

Viewing 3 posts - 1 through 2 (of 2 total)

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