How to handle ¦ character in query
-
I have a number of records in a table with the following string in one of the columns FOOD\r¦ BOX 2 When I copy this string into the where clause (in single quotes) of a select statement in QA on that column, it comes back with no results... Is that special character throwing off the query and if so, how do I search on it?
FYI: When the special character shows in this posting, it is displayed as |, when I look at it in the table it is a black square... very strange
Thanks,
Dan
-
The double pipe "||" is a concantenation character in Oracle (and some other DB systems). It is probably being recognized as a special character and translates out as ASCII Char 124.
If you have no need for the character to be in your table then you can just run an update query and/or place a trigger on insert of
---------------------
update tst_tbl
set test_col = replace(test_col,char(124),char(32))
where test_col like '%|%'
---------------------
As for showing up as a box, that is generally how MS interprets unprintable characters.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Hi Jim,
Thanks for the reply. I tried a select * from table where column like '%' + char(124) + '%' and it returned no rows, so I don't think that is the special character. If I could find out what the character is, I would do the update you suggest.
Dan
-
Given your example,
declare @column varchar(20)
set @column = 'FOOD\r¦ BOX 2'
select ascii(substring(@column, 7, 1))
-- or --
select ascii(substring(column_name, 7, 1))
from my_table
where -- other record identifying stuff here
Good luck!
Steve
-
Thanks Steve, I did just that and found that is was char(166).
Dan
-
Sorry - my bad.
I only had ASCII 0-127 hanging on my wall. I just printed the 128-255 now. The best place I have found for the character set is in MS Access help under the "ASCII" help topic.But now that you have determined the character, the above trigger will still work. You could probably even do multiple queries in one trigger.
Just a side note, when I export text to and from DBs, if I have any control I will use the pipe (Char(128)) as my delimiter. Usually you don't run across it in the data anyway and it works great for the imports. Just my $0.02/4
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
declare @STR as varchar(10)
declare @ptr as integer
declare @ascii as varchar(1000)
set @STR = 'FOOD\r¦ BOX 2'
set @ptr = 1
while @ptr <= len(@str)
begin
print char(ascii(substring(@str,@ptr,1)))
print ascii(substring(@str,@ptr,1))
print '----'
set @ptr=@ptr+1
end
F
70
----
O
79
----
O
79
----
D
68
----
92
----
r
114
----
¦
166
----
32
----
B
66
----
O
79
----
[font="Courier New"]ZenDada[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply