char(9) or char(11) type character

I have a square character in some of my columns in a database which I need t
o
weed out. Problem is, it seems like it is neither a char(9) nor a char(11)
and I have no way of knowing what it is.(old data)
I tried the following for testing purposes.
****************************************
************
CREATE TABLE [Test] (
[Column1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
insert into Test (column1) values ('testvalueStart' + char(11) +
'testvalueend' + char(11))
select charindex(char(11), column1) from Test.
****************************************
******************
The above returns 15, as expected. This is probably because I know what I am
searching for (char(11) in this case). However, in the actual table when I
try charindex for a char(9) or a char(11) on the problematic column, it
always returns a 0.
Are there any other characters that get put in as a square ? Is there any
way of finding out? Or is there a better way of approaching this problem?
Any help is appreciated.
~Naveen> Are there any other characters that get put in as a square ? Is there any
> way of finding out?
DECLARE @.foo VARCHAR(100);
SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
-- otherwise add where clause
DECLARE @.len INT, @.i INT;
SET @.len = LEN(@.foo);
SET @.i = 1;
WHILE @.i <= @.len
BEGIN
PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
SET @.i = @.i + 1;
END|||Thanks Aaron for the sample. Neat thing that I could use sometime.
However, it gives me the ascii values for all the characters and for some
reason it omits the last character which happens to be the problematic one.
By trial and error I found that that the square thingy is a char(0) or a
null, so that'll take care of my problem for now.
Basically what i'm saying is the method fails for a column that has a value
of
'teststring' + char(0). Workarounds for that?
"Aaron Bertrand [SQL Server MVP]" wrote:

>
>
>
>
> DECLARE @.foo VARCHAR(100);
>
> SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
> -- otherwise add where clause
>
> DECLARE @.len INT, @.i INT;
>
> SET @.len = LEN(@.foo);
>
> SET @.i = 1;
>
> WHILE @.i <= @.len
> BEGIN
> PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
> RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
> SET @.i = @.i + 1;
> END
>
>|||Naveen,
you can use the solution
as a pattern for your own one
if it is suitable for you
(you don't post ddl and sample data
therefore i solved the problem
with guess-work about that):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE Seq(seq INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Seq
SELECT ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit);
CREATE TABLE Foo(s VARCHAR(50) NOT NULL PRIMARY KEY);
INSERT INTO Foo(s)
SELECT 'test' + CHAR(0) + '0' + CHAR(0) + '0' UNION ALL
SELECT 'test22' + CHAR(3) + '3' + CHAR(4)+ '4' UNION ALL
SELECT 'test333' + CHAR(6) + '6' UNION ALL
SELECT 'test4444' + CHAR(10) + '10' UNION ALL
SELECT 'test55555' + CHAR(15) + '15' UNION ALL
SELECT 'test666666' + CHAR(21) + '21' UNION ALL
SELECT 'test7777777' + CHAR(28) + '28' UNION ALL
SELECT 'test88888888' + CHAR(36) + '36' UNION ALL
SELECT 'test999999999' + CHAR(45) + '45' UNION ALL
SELECT 'test0123456789' + CHAR(55) + '55' UNION ALL
SELECT 'test01234567890' + CHAR(66) + '66' UNION ALL
SELECT 'test012345678901' + CHAR(0x7F) + '7F' UNION ALL
SELECT 'TEST1' + CHAR(1) + '1';
SELECT F.s, ASCII(SUBSTRING(F.s, S.seq, 1)) AS cs, S.seq AS pos
FROM Foo AS F, Seq AS S
WHERE S.seq <= LEN(F.s)
AND ( ASCII(SUBSTRING(F.s, S.seq, 1)) < 32
OR ASCII(SUBSTRING(F.s, S.seq, 1)) = 0x7F);
DROP TABLE Foo;
DROP TABLE Seq;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)