Replace - various string lengths

[mossbs]

Hi guys,

Not totally sure this is possible - but with some of the brains that are on here - it probably is!

What i need to do is update a column in a table using replace... but i aslo need to move the position of something within the string...

for example...

< 2010 Blah blah blah

will become

Blah Blah blah ? 2010

..if all the strings were the same length and/or the string being replaced was a constant then i know this wouldn't be a problem....

The problem i'm having is that the 'blah blah' is a descrption of a part and therefore is not a constant length.

I think there is a way to 'find' a certain 'string' within a string and calculate its 'position' in the string then work from that - (not sure how to do this - or whether that would work anyway?!)

Any ideas on how to do this?!

Any help greatly appreciated on this!

Cheers!!

 

[gmmastros]

I think you haven't really given us enough information to help you. It appears as though you want to take everything on the left up to the first alpha character, and move it to the right and then change the < to >=. If this is the case, then it really shouldn't be too difficult.

There are some string handling functions in SQL Server that you should learn because it will make life a bit easier when trying to deal with situations like this.

In particular, I'm thinking about PatIndex. With PatIndex, you can specify a "like" type of search, and it will return a integer corresponding to the position within the string.

For example:

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('< 2010 Blah blah blah')

Select PatIndex('%[a-z]%', Data)
From   @Temp

The query above will return 8 because that is the first position within the string that contains a character between A and Z.

We can use this with the left, right, and/or substring function to get each part.

Ex:

Declare @Temp Table(Data VarChar(100))

Insert Into @Temp Values('< 2010 Blah blah blah')

Select PatIndex('%[a-z]%', Data)
From   @Temp

-- Get the left part
Select Left(Data, PatIndex('%[a-z]%', Data)-1)
From   @Temp

-- get the right part.
Select Right(Data, Len(Data)-PatIndex('%[a-z]%', Data)+1)
From   @Temp

The next step would be to put the left and right parts together and do a replace. Like this:

Declare @Temp Table(Data nVarChar(100))

Insert Into @Temp Values(N'< 2010 Blah blah blah')

Select Replace(Right(Data, Len(Data)-PatIndex('%[a-z]%', Data)+1) + ' ' + Left(Data, PatIndex('%[a-z]%', Data)-1), '<', N'?')
From   @Temp
[code]

Finally, we should test this with some variations.  When testing string manipulation code, I like to test with an empty string and also a NULL.  So, something like this:


[code]
Insert Into @Temp Values(N'< 2010 Blah blah blah')
Insert Into @Temp Values(N'Blah blah blah')
Insert Into @Temp Values(N'123456789')
Insert Into @Temp Values(N'')
Insert Into @Temp Values(NULL)

When I added the test code, I noticed a couple problems. The empty string was causing an error. Then, when I added the numbers, I was getting it duplicated. The problems were caused by the pat index function. If the string isn't found, PatIndex will return 0. To correct this problem, we can make sure that PatIndex always returns a value by doing this:

[tt]PatIndex('%[a-z]%', Data [!]+ 'Z'[/!])[/tt]

Putting this all together:

Declare @Temp Table(Data nVarChar(100))

Insert Into @Temp Values(N'< 2010 Blah blah blah')
Insert Into @Temp Values(N'Blah blah blah')
Insert Into @Temp Values(N'123456789')
Insert Into @Temp Values(N'')
Insert Into @Temp Values(NULL)

Select Replace(Right(Data, Len(Data)-PatIndex('%[a-z]%', Data + 'Z')+1) + ' ' + Left(Data, PatIndex('%[a-z]%', Data + 'Z')-1), '<', N'?')
From   @Temp

-George

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