Line Drawing Extended ASCII characters in Text Field
-
Gentlemen, I am trying to insert (and retrieve) extended ascii line drawing characters into a text field. It seems that no matter what I do, the characters are translated into other characters. I've fumbled around with codepages a bit with no results. Can anyone help?
-
Can you post what you've got so far?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, Thanks for the quick Reply!
Basically, this is what I need to do.
UPDATE SomeTableSETTextField = N'+'what seems to happen is that I get a '+' symbol instead. -
You want something like this ?
declare @stmt varchar(11)
set @stmt = 'Hello World'
select N'' + @stmt
-----------
Hello World
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry, note the double ' sign. Doesn't come out clearly.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank, What I'm trying to do is to insert ASCII line drawing chacters into a text field and then retrieve and display them later. I'm using Cold Fusion as an application server. I am able to generate a string with the characters correct, then after I save that string to the text field, it seems that the characters are converted into other chacters. Some of them resemble the characters I tried to save, others don't. Does that make any sense?
-
Ok, that sounds different. Not quite sure if I understand now, but would a look at CHAR and/or ASCII in BOL help?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank, I'll check. I feel so stupid asking this question because it seems so easy, but I'll be darned if I can figure it out!
-
Played a little bit around with it.
set nocount on
if object_id('ascii_art') is not null
drop table ascii_art
go
create table ascii_art( col1 varchar(5))
go
insert into ascii_art (col1) values(ascii('|'))
insert into ascii_art (col1) values(ascii('|'))
select char(col1) from ascii_art
--select char(ascii('|')) +char(10)+ char(ascii('|'))
drop table ascii_art
set nocount off
col1
-----
|
|
If it is that what you want, may I ask why and what for?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
Thats pretty close, My field is a text field. I wonder if that has something to do with it? The reason for wanting to insert linedrawing codes is that I have a customer that is accustomed to seeing a result with a 'graphical' representation of data. This representation consists of some basic ASCII extended characters. This customer is using a legacy system that used the IBM character set that used the upper 128 chacters as graphic characters. the results are something like this.
_____
+-----¦
I was able to replicate this in Cold Fusion and had the string ready to save to the database, but so far, I haven't been able to store the data.
-
Interesting, when I previewed my last post, the characters were displayed properly, but when I posted it, EXACTLY the same type of character conversion took place!
-
Don't take me too serious on that, but you should tell your customer in a polite and kind way that we live in the 21th century and that PC have evolved and he should not stick to mainframe graphics.
You shouldn't try to do this winthin the DB. If he insists on that, do this at the client. I have once tried to do this for a *better* representation of data in QA. Here it is:
SET NOCOUNT ON
DECLARE @col_name char( 255 ),
@out char( 255 ),
@head char( 80 ),
@pad char( 80 ),
@tab char( 30 ),
@r_byte char( 30 ),
@tmp_dat char( 30 ),
@tmp_str char( 30 ),
@t_kb char( 30 )
DECLARE @t_db_size numeric( 10, 0 ),
@r_count numeric( 10, 0 )
DECLARE @col_length int,
@t_col int,
@t_col_size int,
@t_tab int,
@pad_1 int,
@pad_2 int,
@t_rows int,
@c_count int,
@c_size int,
@r_size int,
@tabID int,
@l int
DECLARE @ende bit
SELECT @col_length = 0,
@t_col = 0,
@t_col_size = 0,
@t_tab = 0,
@t_db_size = 0,
@pad_1 = 0,
@pad_2 = 0,
@t_rows = 0,
@c_count = 0,
@r_count = 0,
@c_size = 0
SELECT @pad = ' '
SELECT @head = 'Report für '
+ RTRIM( DB_Name() )
+ ' Stand: '
+ RTRIM( CONVERT( char( 19 ), GetDate() ) )
SELECT @pad_1 = ( ROUND( ( 79 - DATALENGTH( RTRIM( @head ) ) ), 0 ) / 2 )
SELECT @pad_2 = @pad_1
IF ( ( ( @pad_1 * 2 ) + DATALENGTH( RTRIM( @head ) ) ) > 78 )
BEGIN
SELECT @pad_2 = @pad_1 - 2
END
SELECT @out = '['
+ SUBSTRING( @pad, 1, @pad_1 )
+ RTRIM( @head )
+ SUBSTRING( @pad, 1, @pad_2 )
+ ']'
PRINT '==============================================================================='
PRINT @out
PRINT '==============================================================================='
PRINT '| Tabelle | Spalten | Grösse| Zeilen | Bytes |'
PRINT '-------------------------------------------------------------------------------'
DECLARE TableScan CURSOR FOR
SELECT Name,
ID
FROM SysObjects
WHERE Type = 'U'
ORDER BY Name
OPEN TableScan
FETCH NEXT FROM TableScan INTO @tab,
@tabID
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
DECLARE ColumnScan CURSOR FOR
SELECT Length
FROM SysColumns
WHERE ID = @tabID
OPEN ColumnScan
FETCH NEXT FROM ColumnScan INTO @col_length
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT @c_count = @c_count + 1
SELECT @c_size = @c_size + @col_length
FETCH NEXT FROM ColumnScan INTO @col_length
END
DEALLOCATE ColumnScan
SELECT @r_count = MAX( Rows )
FROM SysIndexes
WHERE ID= @tabID
AND ( IndID = 0 OR IndID = 1 )
SELECT @t_col = @t_col + @c_count
SELECT @t_rows= @t_rows+ @r_count
SELECT @r_size = @r_count * @c_size
SELECT @r_byte = RTRIM( CONVERT( char( 30 ), ROUND( ( @r_size ), 0 ) ) )
IF ( PATINDEX( '%.%', @r_byte ) > 0 )
BEGIN
SELECT @r_byte = RTRIM( SUBSTRING( @r_byte, 1, ( PATINDEX( '%.%', @r_byte ) - 1 ) ) )
END
ELSE
BEGIN
SELECT @r_byte = RTRIM( SUBSTRING( @r_byte, 1, DATALENGTH( @r_byte ) ) )
END
SELECT @tmp_dat = RTRIM( @r_byte ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @t_col_size = @t_col_size + @c_size
SELECT @t_db_size = @t_db_size + @r_size
SELECT @t_tab = @t_tab + 1
SELECT @out = '| '
+ RTRIM( @tab )
+ SUBSTRING( @pad, 1, ( 29 - DATALENGTH( RTRIM( @tab ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @c_count ) )
+ SUBSTRING( @pad, 1, ( 8 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @c_count ) ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @c_size ) )
+ SUBSTRING( @pad, 1, ( 6 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @c_size ) ) ) ) )
+ '| '
+ RTRIM( CONVERT( char( 8 ), @r_count ) )
+ SUBSTRING( @pad, 1, ( 8 - DATALENGTH( RTRIM( CONVERT( char( 8 ), @r_count ) ) ) ) )
+ '| '
+ RTRIM( @tmp_str )
+ SUBSTRING( @pad, 1, ( 17 - DATALENGTH( RTRIM( @tmp_str ) ) ) )
+ '|'
PRINT @out
SELECT @col_length = 0,
@c_count = 0,
@c_size = 0,
@r_count = 0,
@r_size = 0
FETCH NEXT FROM TableScan INTO @tab,
@tabID
END
DEALLOCATE TableScan
PRINT '==============================================================================='
PRINT '[ Gesamt ]'
PRINT '==============================================================================='
SELECT @tmp_dat = RTRIM( CONVERT( char( 8 ), @t_tab ) ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @out = '[ Tabellen ] : '
+ RTRIM( @tmp_str )
+ SUBSTRING( @pad, 1, ( 60 - DATALENGTH( RTRIM( @tmp_str ) ) ) )
+ '|'
PRINT @out
SELECT @tmp_dat = RTRIM( CONVERT( char( 8 ), @t_col ) ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @out = '[ Spalten ] : '
+ RTRIM( @tmp_str )
+ SUBSTRING( @pad, 1, ( 60 - DATALENGTH( RTRIM( @tmp_str ) ) ) )
+ '|'
PRINT @out
SELECT @tmp_dat = RTRIM( CONVERT( char( 8 ), @t_col_size ) ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @tmp_dat = RTRIM( CONVERT( char( 8 ), @t_rows ) ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @t_kb = RTRIM( CONVERT( char( 20 ), ROUND( ( @t_db_size / 1024 ), 0 ) ) )
SELECT @tmp_dat = RTRIM( SUBSTRING( @t_kb, 1, ( PATINDEX( '%.%', @t_kb ) - 1 ) ) ),
@tmp_str = ''
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
SELECT @ende = 0
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( @tmp_dat )
SELECT @ende = 1
END
WHILE ( @ende = 0 )
BEGIN
SELECT @tmp_str = ','
+ RTRIM( SUBSTRING( @tmp_dat, @l - 2, 3 ) )
+ RTRIM( @tmp_str )
SELECT @tmp_dat = RTRIM( SUBSTRING( @tmp_dat, 1, @l - 3 ) )
SELECT @l = DATALENGTH( RTRIM( @tmp_dat ) )
IF ( @l <= 3 )
BEGIN
SELECT @tmp_str = RTRIM( SUBSTRING( @tmp_dat, 1, @l ) )
+ RTRIM( @tmp_str )
SELECT @ende = 1
END
END
SELECT @out = '[ Gesamtgrösse] : '
+ RTRIM( @tmp_str )
+ ' KB'
+ SUBSTRING( @pad, 1, ( 57 - DATALENGTH( RTRIM( @tmp_str ) ) ) )
+ '|'
PRINT @out
PRINT '==============================================================================='
SET NOCOUNT OFF
The Output looks like
===============================================================================
[ Report für FRANK_PLAYGROUND Stand: Mär 1 2004 1:30PM ]
===============================================================================
| Tabelle | Spalten | Grösse| Zeilen | Bytes |
-------------------------------------------------------------------------------
| Avgs | 4 | 28 | 6 | 168 |
| def | 3 | 19 | 1 | 19 |
| dtproperties | 7 | 857 | 0 | 0 |
| FUNWITHNULLS | 5 | 73 | 4 | 292 |
| idt | 1 | 4 | 1 | 4 |
| test_q | 3 | 9 | 4 | 36 |
===============================================================================
[ Gesamt ]
===============================================================================
[ Tabellen ] : 6 |
[ Spalten ] : 23 |
[ Gesamtgrösse] : 1 KB |
===============================================================================
Don't look at the code, I'm ashamed, but it uses a cursor
For ASCII art this is nice
http://www.hsk.dk/note/kuriosa/cow-complete.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You seem pretty familiar with the ASCII table, so you might want to look at saving your characters as integers and using char() and ascii() to display and store, respectively.
Look those functions up in BOL and I think you'll find an acceptable answer to what you're wanting to do.
-
Forgot to mention, I would venture a guess that the "conversion" you're seeing is the difference between ANSI characters and ASCII characters. I believe SQL Server works with the ANSI character set by default, so when you hand it a character it looks at it as ANSI. If it's an ASCII character that doesn't have an ANSI equivalent then it would likely get translated into the closest match or whatever.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply