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

Finding due date from a six character long date field

Hi All,
Here is my problem.
I have a datefield as varchar and six characters long.
The date is shown as 200102 which means the entire month of February
in 2001.
I need to find the due date based on this adding 30 days and the date
will be the last date of the month.
For ex.
if the bill date is 200102 the due date will be 03/30/2001
if the bill date is 200112 the due date will be 01/31/2002
and so on.
Could anyone please tell me how can I write the query?
I have given the DDLs below:
CREATE TABLE [dbo].[Table1] (
[ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
)
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200101')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200102')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200103')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200112')
I was trying to run the following query:
SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
INVOICENO='500004'
of course, that did not give me the right answer.
I do highly appreciate your help.
Thanks a million in advance.
Best regards,
mamunHello Magmun !
Try this:
--German Version with dot seperated
Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
left(SUPLBILLDT,4)))) from Table1
--English Version with commy separated
Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
left(SUPLBILLDT,4)))) from Table1
HTH, Jens Süßmeyer.|||How about
SELECT due_date = DATEADD
(
MONTH,
2,
CONVERT(SMALLDATETIME, bill_date + '01', 112)
)-1
FROM table
(I assume you meant 03/31/2001, since 03/30 is not the last day of March.
And also, you're not adding 30 days to 20011201 and then setting the due
date to be the last day of that month, because 20011201 + 30 = 20011231 and
the last day of that month is 12/31, not 20020131. In other words, the
description of your requirements does not match your sample output... so if
the above query doesn't do what you want, you'll need to clarify;
preferably, with actual table structure, sample data (in the form of a
functional insert statement), and desired results.)
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0308220710.1ed91260@.posting.google.com...
> Hi All,
> Here is my problem.
> I have a datefield as varchar and six characters long.
> The date is shown as 200102 which means the entire month of February
> in 2001.
> I need to find the due date based on this adding 30 days and the date
> will be the last date of the month.
> For ex.
> if the bill date is 200102 the due date will be 03/30/2001
> if the bill date is 200112 the due date will be 01/31/2002
> and so on.
> Could anyone please tell me how can I write the query?
>
> I have given the DDLs below:
>
>
> CREATE TABLE [dbo].[Table1] (
> [ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> )
>
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200101')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200102')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200103')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200112')
> I was trying to run the following query:
> SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
> INVOICENO='500004'
> of course, that did not give me the right answer.
> I do highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||Why not return as a datetime, then you can wrap whatever presentation you
want around it using CONVERT and style. In your method, btw, you are
assuming that all of your string concatenations will not break datetime; I'm
not sure this is a healthy assumption, since you may not want to write code
that relies on a specific language / dateformat setting, and the machine's
regional settings.
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:eM$OtKMaDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello Magmun !
> Try this:
>
> --German Version with dot seperated
> Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
> left(SUPLBILLDT,4)))) from Table1
> --English Version with commy separated
> Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
> left(SUPLBILLDT,4)))) from Table1
>
> HTH, Jens Süßmeyer.
>|||OK, convinced :)). Didn´t thought about this solution, even we use it
(Yours, just implemented by ME) to keep track of our guarantee expiry dates
X-)).
Jens Süßmeyer.sql

Finding and removing invisible characters

I need to know how to search every field in a sql database, find
unprintable characters and remove them.
Any ideas?
ChessNut,
I found this on-line somewhere, but I've never used it. It looks like it
would work, but you might need to add more printable character ranges.
update MyTable
set MyColumn = replace(MyColumn, substring(MyColumn, patindex('%[^a-zA-Z0-9
'''''']%', MyColumn), 1), '')
where patindex('%[^a-zA-Z0-9 '''''']%', MyColumn) <> 0
-- Bill
<ImaChessNut@.gmail.com> wrote in message
news:1169825942.277203.203550@.m58g2000cwm.googlegr oups.com...
>I need to know how to search every field in a sql database, find
> unprintable characters and remove them.
> Any ideas?
>

Finding and removing invisible characters

I need to know how to search every field in a sql database, find
unprintable characters and remove them.
Any ideas?ChessNut,
I found this on-line somewhere, but I've never used it. It looks like it
would work, but you might need to add more printable character ranges.
update MyTable
set MyColumn = replace(MyColumn, substring(MyColumn, patindex('%[^a-zA-Z0-9
''']%', MyColumn), 1), '')
where patindex('%[^a-zA-Z0-9 ''']%', MyColumn) <> 0
-- Bill
<ImaChessNut@.gmail.com> wrote in message
news:1169825942.277203.203550@.m58g2000cwm.googlegroups.com...
>I need to know how to search every field in a sql database, find
> unprintable characters and remove them.
> Any ideas?
>

Finding and removing invisible characters

I need to know how to search every field in a sql database, find
unprintable characters and remove them.
Any ideas?ChessNut,
I found this on-line somewhere, but I've never used it. It looks like it
would work, but you might need to add more printable character ranges.
update MyTable
set MyColumn = replace(MyColumn, substring(MyColumn, patindex('%[^a-zA-Z
0-9
''']%', MyColumn), 1), '')
where patindex('%[^a-zA-Z0-9 ''']%', MyColumn) <> 0
-- Bill
<ImaChessNut@.gmail.com> wrote in message
news:1169825942.277203.203550@.m58g2000cwm.googlegroups.com...
>I need to know how to search every field in a sql database, find
> unprintable characters and remove them.
> Any ideas?
>

Find string between 2 characters and insert in different column

I have data in a char column like this:
[1]
[2]
[3]
etc
[100]
[101]
etc
[1000]
etc
I want to select everything between the brackets and insert into a different
column.
Any help please.How about
SELECT REPLACE(REPLACE(YourCol,'[',''),']','')
FROM YourTable
Andrew J. Kelly SQL MVP
"Terri" <terri@.cybernets.com> wrote in message
news:d4p3vr$l0e$1@.reader2.nmix.net...
>I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a
> different
> column.
> Any help please.
>
>|||UPDATE Floob
SET new_foo =
REPLACE ( REPLACE (foobar, '[', ''), ']' '');
or you can do this in a VIEW or computed column.|||As a general approach, you can use the following expression:
SUBSTRING( @.s, CHARINDEX( '[', @.s ) + 1,
CHARINDEX( ']', @.s,
CHARINDEX( '[', @.s ) ) -
CHARINDEX( '[', @.s ) - 1 )
Anith|||Hi Terri,
Try this query...
SELECT Substring(YouCol,2,len(youCol)-2) FROM YourTable
Swami.
"Terri" wrote:

> I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a differe
nt
> column.
> Any help please.
>
>