November 29, 2006 at 3:40 pm
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.
November 29, 2006 at 8:10 pm
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
November 30, 2006 at 8:50 am
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