Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts

Thursday, March 29, 2012

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

Wednesday, March 28, 2012

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 date using PATINDEX in varchar

Hi:
One of the columns in my table is a notes column i.e everytime a user
updates it the date,time, users name and his/her note gets appended in
front. The notes column has text in following format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
I am supposed to filter out the data by each user,its date and time i.e
8/4/2006 Linda King Per MDCR no clm on file,reblld.
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
Any suggestions are welcomed.Hi
DECLARE @.st VARCHAR(100)
SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
SELECT
SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
<sfazili@.gmail.com> wrote in message
news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> Hi:
> One of the columns in my table is a notes column i.e everytime a user
> updates it the date,time, users name and his/her note gets appended in
> front. The notes column has text in following format:
> 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> I am supposed to filter out the data by each user,its date and time i.e
> 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> Any suggestions are welcomed.
>|||Hi Uri:
Thanks for ur reply. Actually the notes field has text in the follwoing
format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
Each update to the notes field just get appended to the field in front.
Firstly, I need to separate each individual update..cant use space as
an identifier... need help here. The thing is that after each
indiviaudal note is identified it should be in follwoing format:
8/4/2006-9:46:37 AM-Linda King-Per MDCR/ no clm on file,reblld
5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22
3/30/2006-10:23:09 AM-Maria James-Patient asks to follow -MEDICARE
After this need to separate each individaul note into thre columns:
8/4/2006 Linda King Per MDCR/ no clm on file,reblld
5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22
3/30/2006 Maria James Patient asks to follow -MEDICARE
Any suggestions appreciated
Uri Dimant wrote:
> Hi
> DECLARE @.st VARCHAR(100)
> SET @.st='8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld'
> SELECT
> SUBSTRING(SUBSTRING(@.st,1,CHARINDEX(' ',@.st)),1,
> CHARINDEX('-',SUBSTRING(@.st,1,CHARINDEX(' ',@.st)))-1)+
> SUBSTRING(@.st,CHARINDEX(' ',@.st),LEN(@.st))
>
>
> <sfazili@.gmail.com> wrote in message
> news:1155574594.397976.247140@.75g2000cwc.googlegroups.com...
> > Hi:
> >
> > One of the columns in my table is a notes column i.e everytime a user
> > updates it the date,time, users name and his/her note gets appended in
> > front. The notes column has text in following format:
> >
> > 8/4/2006-9:46:37 AM-Linda King-Per MDCR no clm on file,reblld.
> > 5/4/2006-9:19:09 AM-Jenny Hopins-MEDICARE PAID $11.37 FOR 3/22.
> >
> > I am supposed to filter out the data by each user,its date and time i.e
> > 8/4/2006 Linda King Per MDCR no clm on file,reblld.
> > 5/4/2006 Jenny Hopins MEDICARE PAID $11.37 FOR 3/22.
> >
> > Any suggestions are welcomed.
> >

Monday, March 26, 2012

finding bad date

I have a database where a varchar field is being used to store dates. I wrot
e
the following function to check if the data is a valid date. If it is not a
valid date, Null is returned.
ALTER function f_ToDate(@.inDate varchar(50))
returns datetime
begin
declare @.returndate datetime
if isdate(@.inDate) = 0
select @.returndate = null
else
select @.returndate = convert(datetime,@.inDate,101)
return @.returndate
end
In my database, I have a table called Generation with a field called
CreateDate. When I run the following query, I get an error message stating
that “syntax error converting datetime from character string”
select dbo.f_ToDate(Generation.CreationDate) from Generation
To check, I ran the following query and it returned all null records which
is expected
select CreationDate from general where isdate(CreationDate) = 0
The following returns no records which is expected.
select CreationDate from general where isdate(CreationDate) = 0 and
CreationDate is not Null.
I can’t find the record that is causing “select
dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.I have had a similar issue w/ Dates. A quick and dirty way that I used was t
o
export out the records using DTS to a table that mirrored the working one.
Only change I made was to change the varchar date field to a true datetime
field in the temp table. In my dts package I set the max number of errors to
the max (999) and started the export.
In my case I had 632 fewer records in my temp table after the export. I ran
a NOT EXISTS against the tables and was able to find the 632 records w/ bad
dates in my production table.
If the number of records w/ bad dates is GT 999, you will have to do the
export in bacthes to find all the bad recs.
Just a thought!
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Emma,
The convert function with style 101 is supposed to be used when converting
from datetime to char or varchar. Can you execute:
select case when isdate(CreationDate) = 0 then null else convert(datetime,
CreationDate)
from Generation
AMB
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Thanks Jay for your response. I did just what you said. The number of record
s
exported to the temp table was the same as in my original table, and the
query work on the temp table and not on the original. I can’t figure it ou
t.
"Jay Kusch" wrote:
> I have had a similar issue w/ Dates. A quick and dirty way that I used was
to
> export out the records using DTS to a table that mirrored the working one.
> Only change I made was to change the varchar date field to a true datetime
> field in the temp table. In my dts package I set the max number of errors
to
> the max (999) and started the export.
> In my case I had 632 fewer records in my temp table after the export. I ra
n
> a NOT EXISTS against the tables and was able to find the 632 records w/ ba
d
> dates in my production table.
> If the number of records w/ bad dates is GT 999, you will have to do the
> export in bacthes to find all the bad recs.
> Just a thought!
> "Emma" wrote:
>|||Thanks. That worked.
"Alejandro Mesa" wrote:
> Emma,
> The convert function with style 101 is supposed to be used when converting
> from datetime to char or varchar. Can you execute:
> select case when isdate(CreationDate) = 0 then null else convert(datetime,
> CreationDate)
> from Generation
>
> AMB
> "Emma" wrote:
>

Wednesday, March 21, 2012

Find UNICODE Data

How can you find UNICODE date within char & varchar fields in table.
I am in the midst of a migration and need to evaluate all the fields that
may have €
chinese, french and thus require more char space in the new structure.
This is a matter of great urgency so any help greatly appreciated.
Thanks
marcIf the fields are not a Unicode data type, then there is no Unicode data in
them. If the characters are not supported by the code page of the field
(defined by its by collation), they are lost (will show up as '?'). If they
are supported by the code page, you can search for them like you normally
would, or by using full-text search.
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:699A4648-85DC-4B5F-A876-6B223CD4A5DE@.microsoft.com...
> How can you find UNICODE date within char & varchar fields in table.
> I am in the midst of a migration and need to evaluate all the fields that
> may have ?
> chinese, french and thus require more char space in the new structure.
> This is a matter of great urgency so any help greatly appreciated.
> Thanks
> marc

Monday, March 12, 2012

FIND OUT IT IS A NUMBER OR STRINg

I have a column with varchar type..
It has some records like this...
isbuluyorum
11222
a1285
12r23
How can i find out it is a number or string ?Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
SELECT isbuluyorum AS is_numeric
FROM your_table
WHERE isbuluyorum NOT LIKE '%[^0-9]%' ;
SELECT isbuluyorum AS is_alpha
FROM your_table
WHERE isbuluyorum LIKE '%[^0-9]%' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||table name =savo
column name=sal type=CHAr
records
sal
saalasdad
12da
122
dasd212
I applied
SELECT sal AS is_numeric
FROM savo
WHERE sal NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE sal LIKE '%[^0-9]%' ;
It returned
is_alpha is_numeric
saalasdad Nothing
12da
122
dasd212
"Savas Ates" <in da club>, haber iletisinde unlar
yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
>|||SELECT sal, isnumeric(sal)
FROM savo
"Savas Ates" <in da club> wrote in message
news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
> table name =savo
> column name=sal type=CHAr
> records
> sal
> saalasdad
> 12da
> 122
> dasd212
> I applied
> SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
> SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
> It returned
> is_alpha is_numeric
> saalasdad Nothing
> 12da
> 122
> dasd212
>
>
> "Savas Ates" <in da club>, haber iletisinde unlar
> yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
http://www.aspfaq.com/show.asp?id=2390
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Sat, 28 Jan 2006 16:40:28 +0200, "Savas Ates" <in da club> wrote:

>table name =savo
>column name=sal type=CHAr
>records
>sal
>saalasdad
>12da
>122
>dasd212
>I applied
>SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
>SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
>It returned
> is_alpha is_numeric
>saalasdad Nothing
>12da
>122
>dasd212
Hi Savas,
Since the datatype is CHAR (not VARCHAR), all values are padded with
spaces. And spaces are not numeric.
Try:
SELECT sal AS is_numeric
FROM savo
WHERE RTRIM(sal) NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE RTRIM(sal) LIKE '%[^0-9]%' ;
Hugo Kornelis, SQL Server MVP|||isnumeric will not work reliably in a situation like this as "numbers" with
an e or a d (I think a few others also) will match as numeric according to
this function.
"Carl Johansen" <carl@._NOSPAM_carljohansen.co.uk> wrote in message
news:drgcjq$r6f$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> SELECT sal, isnumeric(sal)
> FROM savo
> "Savas Ates" <in da club> wrote in message
> news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
>