SQL String manipulation

[Iamthestig]

Hi,

Using T-SQL, can anyone help me convert this string:

'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'

INTO

'German. Speaks Spanish, Dutch, English Fluently, Italian, French Advanced.'

Many thanks

 

[Iamthestig]

Sorry, should have said, this is for SQL 2000.

 

[bborissov]

DECLARE @Test varchar(200)
SET @Test = 'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'

SELECT REPLACE(
          REPLACE(
             REPLACE(@Test, 'Spanish Fluently','Spanish'),
                            'Dutch Fluently'  ,'Dutch'),
                            'Italian Advanced','Italian')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Iamthestig]

Thanks Borislav, not quite what I meant but my fault for not explaining correctly.

The string could be any combination of languages, the only constants being 'Fluently' always precedes 'Advanced'. So I really want to replace all instances of 'Fluently' before the final 'Fluently' and all instances of 'Advanced' except the final 'Advanced'.

So 'Speaks Spanish Fluently, Italian Advanced' remains unchanged but 'Speaks Spanish Fluently, Dutch Fluently, English Advanced, Italian Advanced, French Advanced.' becomes
'Speaks Spanish, Dutch Fluently, English, Italian, French Advanced.'

Sorry for the confusion.

 

[bborissov]

Why not store the values the way you need it?
Also I would never store that data this way, but...
Better way is to have a table with languages, and another table with person Id, Language Id and language level
1 - novice, 2 - Intermediate, 3-Fluent 4-Advanced etc.
That way you queries will be easier . but that is the way I would do it. Now for your problem.

Is this a one time job or should be run more often?
Because if this is a one time job I could post an answer that uses brutal force and it will be SLOOOOW!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Iamthestig]

The data is for a report generated in Word. It will be run many times. It may be easier to manipulate in vba using InStr. I will give that a go first. Thanks for your time.

 

[bborissov]

Try this:
Run this only ONCE:

USE YourDataBase
GO
CREATE FUNCTION dbo.RemoveFirstOccurances(@lcString varchar(8000), @lcStringToBeRemoved varchar(200))
RETURNS varchar(8000)
AS
BEGIN
   WHILE CHARINDEX(@lcStringToBeRemoved, SUBSTRING(@lcString ,CHARINDEX(@lcStringToBeRemoved,@lcString )+1,8000)) > 0
         BEGIN
             SET @lcString = LEFT(@lcString, CHARINDEX(@lcStringToBeRemoved,@lcString)-1)+
                             SUBSTRING(@lcString,CHARINDEX(@lcStringToBeRemoved,@lcString)+LEN(@lcStringToBeRemoved),8000)
         END
    RETURN @lcString
END

Then

---- preparing test data
DECLARE @Temp TABLE (Fld1 varchar(200))
INSERT INTO @Temp VALUES('German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.')

INSERT INTO @Temp VALUES('German. Dutch Fluently, English Fluently, Italian Advanced, French Advanced.')

INSERT INTO @Temp VALUES('German.Italian Advanced, French Advanced.')
----- End preparing
SELECT dbo.RemoveFirstOccurances(dbo.RemoveFirstOccurances(Fld1, 'Fluently'),'Advanced')
FROM @Temp

If that is what you want then just remove the code between
---- preparing test data
and
----- End preparing
and then replace @Temp with your table name.

Of course that function can be better, to allow you to add ALL words you want to remove at once, and take a 3th parameter as delimit char. But I leave that to you


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Qik3Coder]

Not nearly as concise as bborissov, but a lot more fun to puzzle out.

[green]--Function Parameters[/green]
[blue]DECLARE[/blue] @Str [blue]varchar[/blue](200), @StrToReplace [blue]varchar[/blue](200)
[blue]SELECT[/blue] @StrToReplace = [red]' Fluently'[/red], @Str = [red]'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'[/red]
[green]--Begin Function[/green]
[blue]Declare[/blue] @LenOfRemove [blue]int[/blue], @LenOfString [blue]int[/blue]
[blue]SELECT[/blue] @LenOfRemove = [purple]Len[/purple](@StrToReplace), @LenOfString = [purple]Len[/purple](@Str)

[green]--identify last index of 'string to remove' [/green]
[blue]Declare[/blue] @tmpLoc [blue]int[/blue], @LastLoc [blue]int[/blue], @LocToDelete [blue]int[/blue]
[blue]Select[/blue] @tmpLoc = 1, @LastLoc = 0, @LocToDelete = 0
[blue]While[/blue] @tmpLoc > 0
   [blue]BEGIN[/blue] [green]--Get 'next' occurrence[/green]
      [blue]SELECT[/blue] @tmpLoc = [purple]CharIndex[/purple](@StrToReplace, @Str, @LastLoc)
	  [blue]IF[/blue] @tmpLoc > 0 [green]--one exists after the last identified[/green]
         [blue]BEGIN[/blue]
            [blue]SELECT[/blue] @LocToDelete = @LastLoc, @LastLoc = @tmpLoc
            [blue]IF[/blue] @LocToDelete > 0
               [blue]BEGIN[/blue] [green]--A duplicate value exists[/green]
                  [blue]SELECT[/blue] @Str = [purple]LEFT[/purple](@Str, @LocToDelete-1) + 
                  [purple]Substring[/purple](@Str, @LocToDelete + @LenOfRemove, @LenOfString)
                  [blue]SELECT[/blue] @tmpLoc = [purple]CharIndex[/purple](@StrToReplace, @Str, @LastLoc)
                  [blue]IF[/blue] @tmpLoc > 0
                     [blue]SELECT[/blue] @LastLoc = @tmpLoc
               [blue]END[/blue]
         [blue]END[/blue]
   [blue]END[/blue]
[green]--END FUNCTION[/green]
[blue]SELECT[/blue] [red]'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'[/red]
[blue]UNION[/blue]
[blue]Select[/blue] @Str

You need to wrap that code as a Scalar function, and then you can call it like this:

Select t.columnA, dbo.udfRemoveAllButLast(t.ColumnA, ' Fluently'), [b]dbo.udfRemoveAllButLast(dbo.udfRemoveAllButLast(t.ColumnA, ' Fluently'), ' Advanced')[/b]

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Iamthestig]

Hi Borislav,

Yes that works, thank you. But now the user wants a different result!

SELECT dbo.RemoveFirstOccurances(dbo.RemoveFirstOccurances('German. Speaks fluent Spanish, fluent Dutch, fluent English, advanced Italian, advanced French.', 'fluent'),'advanced')

needs to produce

'German. Speaks fluent Spanish, Dutch, English, advanced Italian, French.'

I have tried amending your code, but no luck yet.

Any help much appreciated

 

[Qik3Coder]

@bborissov I'm glad we came up with almost the same exact function. That makes me feel like I might have a clue with what's going on around here. (Took forever to do the color on that though)

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Iamthestig]

Thanks for your help Qik3Coder, I did try your solution but it didn't quite work for me.

I got the result:

'German. Speaks Spanish, Dutch, English, Italian Advanced, French Advanced.'

But wanted,

'German. Speaks Spanish, Dutch, English Fluently, Italian, French Advanced.'

That's by the by now as I now need

'German. Speaks fluent Spanish, Dutch, English, advanced Italian, French.'

 

[Qik3Coder]

Iamthestig,

This is not just flipping a column.
I do not know if the helper functions exist. Which means the clean loop turns into a recursive loop that runs exponentially as many times as there are occurrences. You now have to spool the whole string, find the last. spool the whole string, then spool the list and find the one before that. If the second (earlier in string) one exists, then remove the first (last in string) one.

Bborisov has done most of the leg work for you on this.
It would behoove you attempt the next step on your own.


If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Iamthestig]

I have been attempting this on my own, but I'm not a SQL guru. As I said earlier I manipulated the string in VBA and this worked for me, just thought it would be nice to use SQL for reusability. I am now rewriting my stored procedure to build the string. As I said, thanks for your help.

 

[Qik3Coder]

If you have something, and are having a problem with it, post what you have. It's not a problem to direct you in a better direction, but you should be doing most of the walking.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[bborissov]

OK,
Try this:

[COLOR=blue]USE[/color] YourDataBase
[COLOR=blue]GO[/color]
[COLOR=blue]ALTER[/color] [COLOR=#FF00FF]FUNCTION[/color] dbo.RemoveFirstOccurances(@lcString [COLOR=blue]varchar[/color](8000),
                                          @lcStringToBeRemoved [COLOR=blue]varchar[/color](200),
                                          @bRemoveFirstPositions [COLOR=blue]bit[/color] = 1)
RETURNS [COLOR=blue]varchar[/color](8000)
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
   [COLOR=blue]DECLARE[/color] @RetVal [COLOR=blue]varchar[/color](8000)
   [COLOR=blue]IF[/color] @bRemoveFirstPositions = 1
      [COLOR=blue]BEGIN[/color]
        [COLOR=blue]WHILE[/color] [COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved, [COLOR=#FF00FF]SUBSTRING[/color](@lcString ,[COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved,@lcString )+1,8000)) > 0
                [COLOR=blue]BEGIN[/color]
                       [COLOR=blue]SET[/color] @lcString = [COLOR=#FF00FF]LEFT[/color](@lcString, [COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved,@lcString)-1)+
                                       [COLOR=#FF00FF]SUBSTRING[/color](@lcString,[COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved,@lcString)+LEN(@lcStringToBeRemoved),8000)
                [COLOR=blue]END[/color]
        [COLOR=blue]SET[/color] @RetVal   = @lcString
      [COLOR=blue]END[/color]
   [COLOR=blue]ELSE[/color]
      [COLOR=blue]BEGIN[/color]
          [COLOR=blue]SET[/color] @RetVal   = [COLOR=#FF00FF]LEFT[/color](@lcString, [COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved,@lcString)+LEN(@lcStringToBeRemoved)-1)+[COLOR=red]' '[/color]+
                          [COLOR=#FF00FF]REPLACE[/color]([COLOR=#FF00FF]SUBSTRING[/color](@lcString,[COLOR=#FF00FF]CHARINDEX[/color](@lcStringToBeRemoved,@lcString)+LEN(@lcStringToBeRemoved)+1,8000),@lcStringToBeRemoved,[COLOR=red]''[/color])
      [COLOR=blue]END[/color]

    [COLOR=blue]RETURN[/color] @RetVal
[COLOR=blue]END[/color]

Then try:

[COLOR=green]---- preparing test data
[/color][COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color]([COLOR=red]'German. Speaks Spanish Fluently, Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color]([COLOR=red]'German. Dutch Fluently, English Fluently, Italian Advanced, French Advanced.'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color]([COLOR=red]'German.Italian Advanced, French Advanced.'[/color])
[COLOR=green]----- End preparing
[/color][COLOR=blue]SELECT[/color] dbo.RemoveFirstOccurances(dbo.RemoveFirstOccurances(Fld1, [COLOR=red]'Fluently'[/color],1),[COLOR=red]'Advanced'[/color],1)
[COLOR=blue]FROM[/color] @Temp


[COLOR=blue]DECLARE[/color] @Temp1 [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp1 [COLOR=blue]VALUES[/color]([COLOR=red]'German. Speaks Fluent Spanish, Fluent Dutch, English, Advanced Italian, Advanced French.'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp1 [COLOR=blue]VALUES[/color]([COLOR=red]'German. Fluent Dutch, Fluent English, Advanced Italian, Advanced French.'[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp1 [COLOR=blue]VALUES[/color]([COLOR=red]'German. Advanced Italian, Advanced French.'[/color])

[COLOR=blue]SELECT[/color] dbo.RemoveFirstOccurances(dbo.RemoveFirstOccurances(Fld1, [COLOR=red]'Fluent'[/color],0),[COLOR=red]'Advanced'[/color],0)
[COLOR=blue]FROM[/color] @Temp1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Qik3Coder, about syntax coloring check George's great tool
SQL2TGML:
thread183-1350140

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Qik3Coder]

@bborissov - got the dll, but the exe won't download. Get a 404. Was wondering if you had a copy.

Not sure if it's suitable to throw a call out to george for it.


Sorry to hijack the post.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[gmmastros]

I guess I was a little too aggressive when doing some house cleaning lately. I'm on vacation until Friday. Give this thread a bump on Friday to remind me and I will put it back up.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Qik3Coder,

I just put the application back where it originally was. You can download it here

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

That's awesome.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]