Nulls in the middle of strings

How can I check for Null characters (CHAR(0) ) in the middle of a string?
CHARINDEX doesn't seem to work for it.
Also, can anyone explain why the display of the @.test_string variable is
different in the first and second SELECTs?
Thanks
Damien
DECLARE @.test_string VARCHAR( 25 )
SET @.test_string = 'test' + CHAR(0) + 'test'
SELECT @.test_string
SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
CHAR(0), @.test_string )NULL int he middle of the string ? That sound like "nowhere in the middle of
somewhere" ;-) . AFAIK you can store either NULL or any Values in a column
but not both because NULL means nothing.
Definition: The NULL SQL keyword is used to represent either a missing value
or a value that is not applicable in a relational table.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Damien" wrote:

> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )|||Damien
I think you need SPACE(1) rather than CHAR(0)
DECLARE @.test_string VARCHAR(25)
SET @.test_string = 'test' +SPACE(1)+ 'test'
SELECT @.test_string
SELECT @.test_string,CHARINDEX(SPACE(1),@.test_st
ring)
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:658975D1-6D51-4A87-AB60-5FDDC637FA78@.microsoft.com...
> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )|||-- Sorry guys, I don't think you understand me. Null characters can live in
the middle of strings, I just want to know how to check for them without
looping through every character in the column.
-- I have a string in a CHAR(10) field, it looks like this: 'ADV '
-- When I run the following code on it, I get this:
-- pos char ascii_code
-- 1 A 65
-- 2 D 68
-- 3 V 86
-- 4 0
-- 5 0
-- 6 0
-- 7 0
-- 8 0
-- 9 0
-- And not what I would expect, which is this:
--
-- pos char ascii_code
-- 1 A 65
-- 2 D 68
-- 3 V 86
-- 4 32
-- 5 32
-- 6 32
-- 7 32
-- 8 32
-- 9 32
SET NOCOUNT ON
DECLARE @.pos INT
DECLARE @.test_string CHAR( 10 )
SET @.pos = 1
SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
--SELECT @.test_string = advanced_arrears FROM jlt.pp_additional_information
WHERE client_id = 86840
WHILE @.pos < DATALENGTH( @.test_string )
BEGIN
SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
@.test_string, @.pos, 1 ) )
SET @.pos = @.pos + 1
END
SET NOCOUNT OFF
"Uri Dimant" wrote:

> Damien
> I think you need SPACE(1) rather than CHAR(0)
> DECLARE @.test_string VARCHAR(25)
> SET @.test_string = 'test' +SPACE(1)+ 'test'
> SELECT @.test_string
> SELECT @.test_string,CHARINDEX(SPACE(1),@.test_st
ring)
>
>
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:658975D1-6D51-4A87-AB60-5FDDC637FA78@.microsoft.com...
>
>|||Damien
CREATE TABLE #Test
(
col CHAR(10)
)
INSERT INTO #Test VALUES ('ADV')
SELECT col,len(col),datalength(col)
FROM #Test
--Based on BOL's example
DECLARE @.position int, @.string char(8)
SET @.position = 1
SELECT @.string=col FROM #Test --Only for testing
WHILE @.position <= DATALENGTH(@.string)
BEGIN
SELECT LTRIM(RTRIM(CHAR(ASCII(SUBSTRING(@.string
, @.position, 1))))),
LTRIM(RTRIM(ASCII(SUBSTRING(@.string, @.position, 1))))
SET @.position = @.position + 1
END
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
> -- Sorry guys, I don't think you understand me. Null characters can live
in
> the middle of strings, I just want to know how to check for them without
> looping through every character in the column.
> -- I have a string in a CHAR(10) field, it looks like this: 'ADV '
> -- When I run the following code on it, I get this:
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 0
> -- 5 0
> -- 6 0
> -- 7 0
> -- 8 0
> -- 9 0
> -- And not what I would expect, which is this:
> --
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 32
> -- 5 32
> -- 6 32
> -- 7 32
> -- 8 32
> -- 9 32
>
> SET NOCOUNT ON
> DECLARE @.pos INT
> DECLARE @.test_string CHAR( 10 )
> SET @.pos = 1
> SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
> --SELECT @.test_string = advanced_arrears FROM
jlt. pp_additional_information
> WHERE client_id = 86840
>
> WHILE @.pos < DATALENGTH( @.test_string )
> BEGIN
> SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
> @.test_string, @.pos, 1 ) )
> SET @.pos = @.pos + 1
> END
>
> SET NOCOUNT OFF
>
> "Uri Dimant" wrote:
>
string?
is
CHARINDEX(|||I think you have a tough time ahead of you. The string datatypes are designe
d to handle printable
characters, not control characters. The nul character causes all kinds of pr
oblems. Perhaps because
SQL Server is written in C++, and nul terminates a string in C++? I don't kn
ow, hard to speculate.
Anyhow, you are using the product in a way it isn't intended to be used. You
r best bet is to get rid
of the nul characters (seems like REPLACE isn't an option for this...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
> -- Sorry guys, I don't think you understand me. Null characters can live
in
> the middle of strings, I just want to know how to check for them without
> looping through every character in the column.
> -- I have a string in a CHAR(10) field, it looks like this: 'ADV '
> -- When I run the following code on it, I get this:
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 0
> -- 5 0
> -- 6 0
> -- 7 0
> -- 8 0
> -- 9 0
> -- And not what I would expect, which is this:
> --
> -- pos char ascii_code
> -- 1 A 65
> -- 2 D 68
> -- 3 V 86
> -- 4 32
> -- 5 32
> -- 6 32
> -- 7 32
> -- 8 32
> -- 9 32
>
> SET NOCOUNT ON
> DECLARE @.pos INT
> DECLARE @.test_string CHAR( 10 )
> SET @.pos = 1
> SET @.test_string = 'ADV' + REPLICATE( CHAR(0), 7 )
> --SELECT @.test_string = advanced_arrears FROM jlt.pp_additional_informatio
n
> WHERE client_id = 86840
>
> WHILE @.pos < DATALENGTH( @.test_string )
> BEGIN
> SELECT @.pos, SUBSTRING( @.test_string, @.pos, 1 ), ASCII( SUBSTRING(
> @.test_string, @.pos, 1 ) )
> SET @.pos = @.pos + 1
> END
>
> SET NOCOUNT OFF
>
> "Uri Dimant" wrote:
>|||The @.test_string is just example for this post. The string in question is i
n
an extract from a Mainframe system. It's a fixed width file, and most of th
e
data is padded with spaces as expected. However, a few of the records are
padded with these CHAR(0) characters, and it's causing a minor problem once
imported.
Any suggestions for finding them when they are embedded within another strin
g?
Thanks
"Tibor Karaszi" wrote:

> I think you have a tough time ahead of you. The string datatypes are desig
ned to handle printable
> characters, not control characters. The nul character causes all kinds of
problems. Perhaps because
> SQL Server is written in C++, and nul terminates a string in C++? I don't
know, hard to speculate.
> Anyhow, you are using the product in a way it isn't intended to be used. Y
our best bet is to get rid
> of the nul characters (seems like REPLACE isn't an option for this...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:3557ECBF-3C03-4A6C-BE7A-DE5960AF7A6D@.microsoft.com...
>
>|||Damien, two possible avenues t oinvestigate...
1. Writing a stream filter in your dataaccess laye code, to replace the
char(0)s with some other special character that SQL can handle, BEFIRE
storing the strings in the database, And then another one in the DAL on the
way out to reverse the process (if it's necessary)
2. Redesign your table design so that the data for this field is in a
separate table, Assuming that the current Table this column is in is called
MyTable, and has a integer PKColumn,
Create Table TextValues
(PKColumn integer Not Null,
Sequence SmallInt Not Null,
Value VarChar(7000),
Primary Key (PKColumn , Sequence))
And store the "pieces" of the original string values in this table, without
the char(0)s... one chunk per row, with sequentially increasing sequence
numbers...
"Damien" wrote:
> The @.test_string is just example for this post. The string in question is
in
> an extract from a Mainframe system. It's a fixed width file, and most of
the
> data is padded with spaces as expected. However, a few of the records are
> padded with these CHAR(0) characters, and it's causing a minor problem onc
e
> imported.
> Any suggestions for finding them when they are embedded within another str
ing?
> Thanks
>
>
> "Tibor Karaszi" wrote:
>|||Damien,
I agree that you're not being understood here,
but it is not hard to do what you need.
Convert the string to varbinary and use substring to look
at one character at a time A table of integers to hold all the
string positions will help:
-- Permanent table of integers
create table N8000 (
pos int not null primary key
)
insert into N8000
select 1 + (OrderID-10248) + 830*(ProductID-1)
from Northwind..Orders, Northwind..Products
where ProductID < 12
and (OrderID-10248) + 830*(ProductID-1) < 8000
go
-- sample data
create table S (
pk int primary key,
s varchar(8000)
)
insert into S values
(1,cast(0x48656C6C6F00576F726C64 as varchar(8000)))
insert into S values
(2,cast(0x00000048656C6C6F00000000200020
00 as varchar(8000)))
select pk, pos from N8000, S
where substring(cast(S.s as varbinary(8000)),pos,1) = 0x00
and pos <= datalength(S.s)
-- use datalength so trailing 0x00 bytes are found
go
drop table N8000, S
Damien wrote:

>How can I check for Null characters (CHAR(0) ) in the middle of a string?
>CHARINDEX doesn't seem to work for it.
>Also, can anyone explain why the display of the @.test_string variable is
>different in the first and second SELECTs?
>Thanks
>
>Damien
>
>DECLARE @.test_string VARCHAR( 25 )
>SET @.test_string = 'test' + CHAR(0) + 'test'
>SELECT @.test_string
>SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
>CHAR(0), @.test_string )
>|||FYI your example works as expected in yukon beta 2
"Damien" wrote:

> How can I check for Null characters (CHAR(0) ) in the middle of a string?
> CHARINDEX doesn't seem to work for it.
> Also, can anyone explain why the display of the @.test_string variable is
> different in the first and second SELECTs?
> Thanks
>
> Damien
>
> DECLARE @.test_string VARCHAR( 25 )
> SET @.test_string = 'test' + CHAR(0) + 'test'
> SELECT @.test_string
> SELECT @.test_string, ASCII( SUBSTRING( @.test_string, 5, 1 ) ), CHARINDEX(
> CHAR(0), @.test_string )