Replace chr(10) with chr(13)+chr(10) 2

[edelwater]

Hello, some of my textfields are copy and pasted from other appllications and only contain chr(10) as cr. In my application (Rational ClearQuest) this shows as a little block so i want to globally replace all chr(10) which are not preceded with chr(13) with chr(13)+chr(10).

THIS DOES NOT WORK:

declare @NewLine1 char(2);
declare @NewLine2 char(1);
declare @NewLine3 char(1);
set @NewLine1=char(13)+char(10);
set @NewLine2=char(10);
set @NewLine3=char(13);

update Defect
     set description = Replace(description , @NewLine2,'')
    where id='sb00012845';

update Defect
     set description = Replace(description , @NewLine3, @NewLine1)
    where id='sb00012845';

i dont know if it will work but it currently gives me :

Argument data type text is invalid for argument 1 of replace function.
Argument data type text is invalid for argument 1 of replace function.

Can anyone help me?






--

http://leau.net

 

[AlexCuse]

I know in VB you use chr(10) and chr(13)

char(number) is a data type.

A wise man once said
"The only thing normal about database guys is their tables".

 

[gmmastros]

Alex,

Char can also be used to represent ASCII values.

Select Char(65) [green]-- Returns "A"[/green]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

clicked submit too fast. I think you need to look into what the codes are for SQL server to recognize what you are trying to say, I'm not sure what they are.

Let me know what you find out,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

Alex,
There is CHAR() function which is similar to CHR() in VB. So
edelwater is used it when He/She initialise variables.
edelwater, REPLACE function doesn't works on TEXT fields. I suspect that Description field is a TEXT one.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[edelwater]

Yes my first problem is the TEXT columns.

However i want to try something like:

http://sqlserver2000.databases.aspf...ace-within-an-ntext-column-in-sql-server.html

for that.

However this example is for NTEXT and simple replacement of 1 string to another string. So my second problem is that i cannot simply replace CHR(10) with CHR(13)+CHR(10) since this will also replace the "good ones" to CHR(13)+CHR(13)+CHR(10) causing and endless loop ergo IF CHARINDEX(@oldString, @newString) > 0 *terminate*

So i probably will have to loop through each textfield and take 2 bytes (WHILE). if the first byte is a CHR(13) and the second CHR(10) then it is correct. if the first byte is NOT chr(13) and the second byte is a CHR(10) THEN i will have to insert a new byte (CHR(13)) right before the CHR(10)

This looks like quite some work so that was why i was wondering if someone has ever done this before since there are quite some steps to take.







--

http://leau.net

 

[gmmastros]

PatIndex may help. It allows you to be fancy with your pattern matching and also works on text columns. Take a look at this example:

Declare @Temp Table(Id Int, Data TEXT)

Insert Into @Temp Values(1, 'Hello' + Char(13) + Char(10) + 'World')
Insert Into @Temp Values(2, 'Hello' + Char(10) + 'World')

Select PatIndex('%[^' + Char(13) + '][' + Char(10) + ']%', Data)
From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Thanks guys. I find a way to embarass myself in here every day! At least it usually means I learn something new.

Edelwater - not sure what kind of implementation here would be best for you, but what I do with VBScript for problems like this is first replace char(13) with nothing, then replace char(10) with char(13)+char(10). That may or may not be helpful to you here.

I would be interested to know what you come up with.

Alex



A wise man once said
"The only thing normal about database guys is their tables".

 

[edelwater]

thanx gmmastros , didnt know patindex could do regex this will make life simpler.

thanx AlexCuse, in my head i had it the other way around, but if this is the pattern i will use it, great tip.

--

http://leau.net

 

[gmmastros]

edelmater,

PatIndex is similar to regex, but it's not the same. Take a look in Books On Line to see what the differences are.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[edelwater]

The strange this is... i wanted to use UPDATETEXT but on MSDN it states:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new
development work, and plan to modify applications that currently use this feature. Use the large-value data
types and the .WRITE clause of the UPDATE statement instead.

But when i travel to the UPDATE .WRITE clausule it states:

.WRITE ( expression, @Offset , @Length )
Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause.

UH??? So .WRITE supports no "text" and UPDATETEXT is nog longer advised to use... STRANGE!


--

http://leau.net

 

[gmmastros]

If you are using SQL 2000, then use UpdateText.
If you are using SQL 2005, then the data type should be VarChar(max) instead of text.

This just highlights the fact that Microsoft will *probably* drop the Text data type in the next version or 2. When that happens, you will need to re-write the code you use to update the value of your text field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[edelwater]

Ah...well actually i am using the database of Rational ClearQuest (2003.06.15) which happens to be SQL Server with this client. Since i am probably not allowed to change the database schema and the next version (Baltic, version 7) will be rolled out sooner or later it could well be that ibm/rational replaced TEXT with VARCHAR in version 7 (i have to look that up).

So... i am going for a different strategy, im going to use a hook in the api of the rational application either in perl or vb (but i think vb since the client has this put up as default in their clearquest environment).

Ah...well...another afternoon.

Well... I learned a lot from you guys, thank you and so much thanks for you quick response in this forum.








--

http://leau.net

 

[AlexCuse]

but if this is the pattern i will use it

I may be wrong on which character is which (and probably am), what I wrote in the past was to replace vbLf with nothing, then replace vbCr with vbCrLf. I just thought it may be easier for you to just strip out the component that is not appearing every time, allowing you to do an easier replace on the one that is (rather than looking at two bytes at a time).

I get some horrendously delimited dat files, and use this to prep them for DTS packages (the alternative being to open with word pad, then save as text... not fun).

Hope everything works out well for you on this.

Alex

A wise man once said
"The only thing normal about database guys is their tables".