Mysterious symbol in text field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Mysterious symbol in text field

Author

Topic

hmusa
Starting Member

36 Posts

Posted - 2002-03-26 : 10:49:00

when insering text into a text field i get a square before and after the text, does anyone know how they got there or why they are there and how i get rid of them?

Edited by - hmusa on 03/26/2002 10:49:21

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-26 : 11:14:38

What characters are they? If they are the first and last character in the column, you should be able to see the character code with ASCII (or UNICODE if they are nchar or nvarchar type columns):

SELECT ASCII(LEFT(text_column,1)), ASCII(RIGHT(text_column,1))
FROM your_table

you may need to put an RTRIM in to get the last character if the column is char rather than varchar. The empty square glyph is used in Windows when there is no (suitable) glyph for the character. In Query Analyzer, this includes the control characters 2-8, 11, 12, 14-31, 127.

Edited by - Arnold Fribble on 03/26/2002 11:15:29

hmusa
Starting Member

36 Posts

Posted - 2002-03-26 : 11:22:08

The data type of the field is text and threfore I cannot use the query that you gave me , any other ideas, i am entering the information from as ASP page but have checked all that and cannot see where the problem is.

Thanks in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-26 : 11:24:00

Can you post your ASP code, where you perform the INSERT operation?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-26 : 11:27:39

~~You can get the left one, at least, by casting your text column to varchar, and the right one too if it doesn't get truncated by the 8000 character limit.~~

Edit: Scratch that! Use SUBSTRING and DATALENGTH, then there's no character limit:

SELECT ASCII(SUBSTRING(text_column, 1, 1)), ASCII(SUBSTRING(text_column, DATALENGTH(text_column), 1))
FROM your_table

Edited by - Arnold Fribble on 03/26/2002 11:36:53

hmusa
Starting Member

36 Posts

Posted - 2002-03-26 : 11:32:55

here are the extracts from the asp code

this is the text i am trying to enter into the database:

<table>
<tr><td CLASS="W50">Headline :<td><input CLASS="W350" NAME="HEADLINE" TYPE="TEXT">
</table>
<table><td CLASS="W50">Date : <td><input CLASS="W100" NAME="RELEASEDATE" TYPE="TEXT">
</table>
<%
%><table><tr><td><textarea NAME="BODYTEXT" growable WRAP="SOFT" ROWS="26" COLS="80">
<% Response.Write "OFEX Market Notice" & " " & sMarketNoticeNumber & chr(13) & chr(13) & sheadline & chr(13)& chr(13)& sname & "," & sTitle & CHR(13)& "OFEX Plc" & CHR(13) & "No 1 Goodmans Yard" & CHR(13)& "LONDON E1 8AT" & CHR(13) & CHR(13) & "Tel: 020 7422 0800" & _
CHR(13) & "Fax: 020 7423 0889"
%>
</textarea></tr>
</table>
<BR><%LastModifiedDetails ocn,sLastModifiedDate,sLastModifiedUser

here is the code that runs the stored procedure:

if sreleaseDate <> "" then
ssql = "spOFEXMarketNotices_Update " & _
"@ReleaseDate=" & spParam(sReleaseDate) & "," & _
"@Headline=" & spParam(PadQuotes(sHeadline)) & "," & _
"@BodyText=" & spParam(PadQuotes(sBodyText)) & "," & _
"@OFEXMarketNoticesID=" & spParam(PadQuotes(sOFEXMarketNoticesID)) & "," & _
"@MarketNoticeNumber=" & spParam(PadQuotes(sMarketNoticeNumber))
ocn.execute ssql

hmusa
Starting Member

36 Posts

Posted - 2002-03-26 : 11:41:52

Arnold

thanks for your ideas I have found that it is inserting a tab before the text and after.

Many Thanks

hmusa
Starting Member

36 Posts

Posted - 2002-03-27 : 05:48:19

i thought i had this worked out but i have'nt, i tried replacinfg the tab in the asp code with the following code:

sheader = replace("OFEX Market Notice" & " " & sMarketNoticeNumber & chr(13) & chr(13),chr(9),chr(8))

but it does not work i then tried replacing the tab in the sql statement using:

replace(@ReleaseDate, char(9),char(8))

still no joy

any ideas as to how i can get rid of this character!

thanxs in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources