Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

removing Square Symbol from sql text column

Posted on 2007-08-07
13
Medium Priority
?
1,992 Views
Last Modified: 2008-06-03
I'm using SQL 2005 and have a table with unique id and a text field:
tbl_account_notes
note_id
note_text

From an import of an old system, there are lots of little square symbols that appear to act as line breaks when displayed on a web page.
I have tried replace scrips that look for char(10) and char(13) which remove something, but not the squares.
The characters are causing problems in my web application with the display in css.

I can remove them manually simply by copying into a text editor and then pasting back into sql. However, there are 400,000+ of these and I think that would be a little tedious.

Regards
Andrew
0
Comment
Question by:andrewh123
  • 6
  • 6
13 Comments
 
LVL 5

Expert Comment

by:kvimal
ID: 19644742
If you know equivalent ascii of the square symbol, then it is easy for you to remove those symbols using code.
0
 
LVL 1

Author Comment

by:andrewh123
ID: 19644761
Hi Kvimal - could you be a bit more specific?
How do I find the ascii value?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19644805
find the location of that square in your data row and then use
asccii(substring(yourcolumn, locationofthatsquare,1))

then later use replace that

update yourtab set yourcol = replace(yourcol, ascii(value),'')
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:andrewh123
ID: 19644984
Hi Imran fast
THe resulting value of this is a zero 0.
I can't do a replace on this as it is a text column.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19645031
What is the data type for your column. text or varchar

you can try this
update yourtab set yourcol = replace(yourcol, 0,'')




0
 
LVL 1

Author Comment

by:andrewh123
ID: 19645113
Hi again,
the data type is text.

The ascii value returned was 0.
so I tried
update tbl_account_notes
set  note_text= replace(note_text,ascii(0),'')
where (account_id = 3245)

but got an error about the wrong datatype
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19645177
can you check the datalength of the column
like
select max(datalength(yountextcolumn)) from yourtable
if it is less than 4000 change it to varchar(4000) and then try.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19645183
oh you have sql 2005 change the datatype for your column to varchar(max) and then try replacing.
0
 
LVL 1

Author Comment

by:andrewh123
ID: 19645317
The update doesn't fail anymore but the little squeares are still there
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19675020
It means you have to double check the ascii code for the square. check the code for each and every character in your data column and see what is for the square.
0
 
LVL 1

Author Comment

by:andrewh123
ID: 19682494
Hi there
I'm certain it was the correct code - it just came out as ascii(0)
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 19682584
ok then check the unicode for the character UNICODE (character)
0
 
LVL 1

Author Comment

by:andrewh123
ID: 19684359
Thaks for your help - much appreciated.
The unicode character comes out as 0 as well and the update for this character has no affect:

SELECT     unicode(substring(note_text, 31, 1))
                           FROM         tbl_account_notes
where note_id=7717

=0

update tbl_account_notes
 set note_text = replace(note_text, unicode(0),' ')
where note_id=7717

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question