Finding illegal characters

I'm trying to find all occurances of bad data in a varchar column. The bad
characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
I need a quick and dirty way to do this.
I tried:
--Doesn't work...
WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
--Works, but too much code.
WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
Ideas Anyone?It might be better to identify and define the characters you want to allow,
rather than the opposite.
What's nice about this approach is thatyou can indicate A-Z, a-z and 0-9 in
a range. Just add any other allowable characters to the end.
CREATE TABLE #foo
(
bar VARCHAR(32)
)
INSERT #foo(bar) SELECT 'legal';
INSERT #foo(bar) SELECT 'not legal -- ~?@.#@.#';
-- find the legal ones
SELECT * FROM #foo
WHERE PATINDEX('%[^A-Za-z0-9]%', bar) = 0
-- find the bad ones
SELECT * FROM #foo
WHERE PATINDEX('%[^A-Za-z0-9]%', bar) > 0
DROP TABLE #foo
"Billg_sd" <Billgsd@.discussions.microsoft.com> wrote in message
news:85320AA5-EA1F-4C3E-A4BB-CB85ECA6D17D@.microsoft.com...
> I'm trying to find all occurances of bad data in a varchar column. The
> bad
> characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
> I need a quick and dirty way to do this.
> I tried:
> --Doesn't work...
> WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
> --Works, but too much code.
> WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
> Ideas Anyone?|||Billg_sd (Billgsd@.discussions.microsoft.com) writes:
> I'm trying to find all occurances of bad data in a varchar column. The
> bad characters include: /";:<>|[],.'?~`!$%^&*()-+={}\
> I need a quick and dirty way to do this.
> I tried:
> --Doesn't work...
> WHERE MyColumn LIKE '%[/";:<>|[],.''?~`!$%^&*()-+={}\]%'
> --Works, but too much code.
> WHERE MyColumn like '%[|]%' OR MyColumn LIKE '%[[]%' OR ....
Here's variation that works:
WHERE a LIKE '%[-[/";:<>|,.''?~`!$%^&*()+={}\]%' OR a like '%]%'
There were two problems in the original string:
1) The hyphen must come first, or else it denotes a range.
2) The right bracket must be tested for separately.
As Aaron says, it may be better to test for permitted characters instead,
but that may be devilish too. For instance, his example was:
'%[^A-Za-z0-9]%'
But the result of this range depends on the collation. For instance,
in a Finnish-Swedish collation it would match the word "coperative",
whereas it would not in many other collations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find Non-Matching End Points

Hello,

I'm trying to manipulate some data to find occurances where a certain end point it missing. For example, in my table I have the following record:

Sample

SHAPE_ID

SEQ_NUM

X_COORDINATE

Y_COORDINATE

82

0

606494.606

4820354.142

82

1

606487.996

4820344.571

82

2

606512.455

4820319.828

82

3

606590.877

4820420.208

82

4

606611.069

4820404.432

82

5

606634.138

4820434.514

82

6

606614.812

4820449.179

82

7

606670.587

4820520.016

82

8

606638.161

4820546.216

82

9

606500.606

4820400.142

For shape_ id = 82 (I have thousands of these to process), Seq_num = 0 is the beginning of the item, while seq_num 9 is the end point. The end point (x_coordinate/y_coordinate)i.e. Seq_num=9 is supposed to match the begin x_coordinate/y_coordinate values, but in this example, they do not match.

How do I find each occurance of the same shape_id where the end seq_num (#9, or whatever number is has been incremented to) x_coordinate/y_cooordinate, does not match the begin x_coordinate/y_coordinate seq_num (#0) ?

As well, if any of these records are indeed found, how do I increment to the next highest sequence number and update that record with the begin point (seq_num=0) x_coordinate/y_coordinate values?

I would appreciate any help with this problem....Thx. in advance...Walt

Moving to the Transact-SQL forum. I believe that's better equipped to provide an answer.

|||

Walt:

Is this close to what you are looking for:

set nocount on

declare @.sample table
( shape_id integer,
seq_num integer,
x_coordinate numeric (12,3),
y_coordinate numeric (12,3),
primary key (shape_id, seq_num)
)
insert into @.sample values (82, 0, 606494.606, 4820354.142 )
insert into @.sample values (82, 1, 606487.996, 4820344.571 )
insert into @.sample values (82, 2, 606512.455, 4820319.828 )
insert into @.sample values (82, 3, 606590.877, 4820420.208 )
insert into @.sample values (82, 4, 606611.069, 4820404.432 )
insert into @.sample values (82, 5, 606634.138, 4820434.514 )
insert into @.sample values (82, 6, 606614.812, 4820449.179 )
insert into @.sample values (82, 7, 606670.587, 4820520.016 )
insert into @.sample values (82, 8, 606638.161, 4820546.216 )
insert into @.sample values (82, 9, 606500.606, 4820400.142 )
--insert into @.sample values (82,10, 606494.606, 4820354.142 )

--select * from @.sample

select a.shape_id,
a.seq_num,
a.x_coordinate as [Last X],
a.y_coordinate as [Last Y],
z.x_coordinate as [Zero X],
z.y_coordinate as [Zero Y]
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

-- shape_id seq_num Last X Last Y Zero X Zero Y
-- -- -- -- -- -- --
-- 82 9 606500.606 4820400.142 606494.606 4820354.142

update @.sample
set x_coordinate = a.x_coordinate,
y_coordinate = a.y_coordinate
from ( select shape_id,
seq_num,
x_coordinate,
y_coordinate
from @.sample p
where seq_num
= ( select max (seq_num) from @.sample q
where p.shape_id = q.shape_id
and q.seq_num > 0
)
) a
inner join @.sample z
on a.shape_id = z.shape_id
and z.seq_num = 0
and ( a.x_coordinate <> z.x_coordinate or
a.y_coordinate <> z.y_coordinate
)

select * from @.sample where seq_num = 0

-- shape_id seq_num x_coordinate y_coordinate
-- -- -- -- --
-- 82 0 606500.606 4820400.142

Dave

|||

Dave,

Thanks for your prompt reply. I'll hopefully give your code a try tomorrow.

Is this a sql statement that I can paste into the Sql View of a new sql statement?

Sorry for such a dumb question......Walt

|||

Dave,

I'm in the process of importing my table into my Oracle schema and am just in the process of creating a new table.

I have over 500,000 records to load so I'll give your reply a try.

thx again....Walt