CharIndex, Replace, Substring not work with Diacretic why ?

Question

CharIndex, Replace, Substring not work with Diacretic why ?

shashi kant

SSCommitted

Points: 1659
More actions
January 20, 2007 at 3:50 am

#115824

My problem is that i have a string which is {PN}?Hello{PN}' and when i want to remove the {PN} from the string using replace it replace the last{PN} but not the first one . i don't no why but i just use diacretic ahead of Hello.
and the same thing is happing with me when i find the char index of {PN} it give me the char index of the last {PN} I also check all thing but don't no why this happing........
I send my example code below ..............................
my test string is
Declare @vText nchar(1000)
@vText='{PN}?Hello{PN}' 'the diacretic is the comma(') befor the character h in Hello
Select @iPos1 = CharIndex('{PN}', @vText)
       IF @iPos1 > 1
       Begin   SELECT @iPos3 = @iPos1
           SELECT @iPos1 = @iPos1 + 1
           SELECT @iPos2 = CharIndex('{PN}', @vText, @iPos1)
           SELECT @iPos4 = @iPos2 + 4
           SELECT @iPos1 =@iPos1 + 3
           SELECT @iPos2 =@iPos2 - @iPos1
           SELECT @iPos4 = @iPos4 - @iPos3
/////I am getting the problem at this point bcoz not getting the POS1 value, due to which I am getting the problem "invalid length passed to the substring function
           SELECT @vText1 = SUBSTRING(@vText, @iPos1, @iPos2 )
           SELECT @vText1 = LTRIM(@vText1)
           SELECT @vText1 = ' ' + @vText1

           Update FinalGetWordStringDump
               Set NHeading = @vText1, IsAuthorHeading = @iIsAuthorHeading
               Where Computer = @vComputer AND
                   DKNo = @vDKNo
           SELECT @vText2 = SUBSTRING(@vText, @iPos3, @iPos4 )
           Set @vText = REPLACE(@vText, @vText2, '')

           Update FinalGetWordStringDump
               Set Text = @vText
               Where FieldName = 'Heading' AND
                   Computer = @vComputer
                   AND DKNo = @vDKNo
       End
<script></script>
Experts must see this problem and how can i replace data when it is of Nvarchar datatype..........
Substring, Replace, CharIndex, PatIndex all fail in my case.....
Shashi Kant

Viewing 5 posts - 1 through 5 (of 5 total)

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

You are using the start position by the variable @iPos1, which holds the first occurence position of require character. That why I get the position of the next occurence rather then the first one

cheers

shashi kant SSCommitted Points: 1659 More actions · Answer 2

Hi ijaz

I am not clear on your view can u tested my code in ssms or query analyzer ...

i have problem for substring when there is a diacretic in the {PN}?Hello{PN}'

here ? is a diacretic in this text editor it is not shown , u can use arial ms unicode font and try to solve this in SSMS or query analyzer......

and then give me the answer or give me alternate method to solve this .

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Shashi,

I tried to test your code, but you didn't declare most of the variables and are updating a table which is unknown to me... so I tried to guess the variables and commented out the update.

Also, SET command is missing from the @vText='{PN}?Hello{PN}'.

I wasn't able to understand what you are doing in the code. Is it just that you want to replace all occurences of {PN}? It seems more like you want to extract everything that is between two occurrences of this string. Please try to describe what you want to do, and tell us precisely what character (character code, like CHAR(19)) this "diacretic" is. Maybe it is a single quote, which would then mess up the string?

One thing I noticed : Your code says "IF @ipos1 > 1 BEGIN..." Isn't that why the first occurence of the string does not behave as you expect?

I'm sorry, but I can't help at the moment - try to tell us more, then I'll try again if I can (I'll be off Internet access for a few days, beginning this afternoon).

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Just an idea... this might help you, if you want to fetch the text that is between two occurences of the string {PN}. It only works with first two, if there can be several than you have to modify the code - depending on how it should work (which I don't know).

DECLARE @vText nchar(1000)

DECLARE @vText1 nchar(1000)

SET @vText='sometext{PN}?Hello{PN}someothertext' /*the original string*/

SET @vtext1 = RIGHT(@vtext, LEN(@vtext)-CHARINDEX('{PN}', @vText)) /*string after first occurence of {PN} is removed*/

SELECT SUBSTRING(@vtext, 4+CHARINDEX('{PN}', @vText), 6+CHARINDEX('{PN}',@vtext1)) /*result - text between the two {PN}*/