Binary to Varchar

I am trying to conver a binary column in the source table to a varchar as sh
own
select convert(varchar(32),0x00000000000003F3) it retunrns a null
hoe do i go about doing it?
Thankssatya wrote:
> I am trying to conver a binary column in the source table to a varchar as
shown
> select convert(varchar(32),0x00000000000003F3) it retunrns a null
> hoe do i go about doing it?
The following works for me:
select convert(varchar(32), convert(int, 0x00000000000003F3))

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
--
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+Isql

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Bigint stored as varchar has issues...

Hi,
I'm importing contact information into SQL Server from Excel using
OPENROWSET. The issue I'm having is with how the phone numbers get stored.
The phone numbers have no extra characters like dashes, so they appear like
9495551212, which is equivelant to 949-555-1212. The phone number is being
imported to a varchar field, which implicitly converts it to a format like
7.70947e+009. The final destination field is intended to hold the data as it
is originally, so it's a bigint datatype.
My first thought was to use CAST or CONVERT. But I get:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Any ideas?Try,
select str(cast('7.70947e+009' as float), 10, 0)
AMB
"Eric" wrote:

> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear lik
e
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Problem #1 7.70947e+009 is not a representation of an integer. It is
expressed with exponentials, so it is a float. You first have to cast it
to a float:
select cast(cast ('7.70947e+009' as float) as bigint)
Problem #2,( and this is the kicker) The result will be:
7709470000
Which is probably not a valid phone number. You are losing some of the
significant digits. Try casting it ot a bigint first, then to a
varhchar(10).
SELECT cast(cast(phonenumber as bigint) as varchar(10))
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
This worked for me.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||That did the trick! Thank you so much...
"Louis Davidson" wrote:

> Problem #1 7.70947e+009 is not a representation of an integer. It is
> expressed with exponentials, so it is a float. You first have to cast it
> to a float:
> select cast(cast ('7.70947e+009' as float) as bigint)
> Problem #2,( and this is the kicker) The result will be:
> 7709470000
> Which is probably not a valid phone number. You are losing some of the
> significant digits. Try casting it ot a bigint first, then to a
> varhchar(10).
> SELECT cast(cast(phonenumber as bigint) as varchar(10))
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
> This worked for me.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
>
>|||You could try adding IMEX=1 to the connection properties to see if that
helps.
select *
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\filename.xls;HDR=YES;IMEX=1;'
,Sheet1$
)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Hi
Make sure that on the Excel spreadsheet, the column is not "general" but
rather a "text" type cell.
General cells are evaluated by the Excel driver and if they meet numeric
criteria, they are passed as numeric to SQL Server. This is an Excel issue.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?

BETWEEN returns invalid data

I'm using BETWEEN stmt to retrieve data from a varchar column code,
so suppose this column contains mainly numbers,( i.e. 40, 300, 400, 500)
and the stmt is:
Select * from table where code between '300' and '500'
the result returns 40 also, so this is not an effective way to retrieve such data.
so how to make this work out.

Best Regards,

Quote:

Originally Posted by theavitor

I'm using BETWEEN stmt to retrieve data from a varchar column code,
so suppose this column contains mainly numbers,( i.e. 40, 300, 400, 500)
and the stmt is:
Select * from table where code between '300' and '500'
the result returns 40 also, so this is not an effective way to retrieve such data.
so how to make this work out.

Best Regards,

Try Greater than 300 and less than 500 like the following:
Select * from table where code > '300' and code < '500'

what do you get?|||Hi ,

Plz try this . . .

Select * from table where convert(numeric,code) between 300 and 500. This query will wrk properly. or else mail me at mneduu@.gmail.com|||PLease try this

Select * from table where code between 300 and 500. This query will work.
I've already try this

and this quesry will also work

Select * from table where convert(numeric,code) between 300 and 500.

BETWEEN A AND F

Hi All.
Intervals on varchar fields.
I just want to pick all customers from my DB who has Customernames starting
with A, b, c, d, e, f. Some kind of BETWEEN A AND F. (Starting letters).
Any ideas.
Even better. I have the query ... WHERE CustomerName LIKE @.CustomerName. Is
there any combinations of %, <, > or any other characters that can give me
all customers between A and F. (E.g...WHERE CustomerName LIKE 'A-F' or
something...
Thanx all
GeirLook up wildcards in the books online; you basically want a variation
of :
WHERE CustomerName Like '[a-f]%'
Stu|||Just what I was looking for. Thanx a lot!
Still learning.....:-)
-gh
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126525507.534946.152760@.f14g2000cwb.googlegroups.com...
> Look up wildcards in the books online; you basically want a variation
> of :
> WHERE CustomerName Like '[a-f]%'
> Stu
>|||Been there :)
Books Online will be your best friend if you're trying to learn the
distinctive characteristics of SQL Server. Of course, if means you
have to know what you're looking for because it's an encyclopedia, not
a textbook.
Stu

Best way to restrict a varchar field not to contain spaces?

Hi,

is this the best way?

ALTER TABLE MyTable
ADD CONSTRAINT CK_MyTable_NoSpace
CHECK (charindex(' ', MyColumn)=0)

Is there any other way?

Thnx

Not sure but you can also try NOT LIKE '% %'

Try them both. What is the reason you need this?

|||

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

SimonSa wrote:

What is the reason you need this?

File system entries. The OS allows them but I want to restrict them. It is also being done in the DAL.

|||

Carl M. wrote:

Thanks Simon

SimonSa wrote:

NOT LIKE '% %'

It would have to be trimmed first no?

or maybe:

NOT LIKE '% %' AND NOT LIKE '% ' AND NOT LIKE ' %'

No. % can stand for any number of characters, even none. NOT LIKE '% %' would be correct.

|||Thank you.

Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
ky
Hi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegro ups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>
|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
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
|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegro ups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>
|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.

Best way to get the next auto number

Hi all, let say I have a table:
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
How do I know the next value that MyID gonna have before inserting a
new record?
What is the best way to do that?
Get the max number plus one? Wouldn't it be expensive to do it this
way?
Thanks
--
kyHi
CREATE TABLE test (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL
)
DECLARE @.nextid INT
INSERT INTO test([Name]) VALUES ('John')
SELECT @.nextid=SCOPE_IDENTITY()
Print 'Next id is '+CAST(@.nextid+1 AS VARCHAR(10))
DROP TABLE Test
--Or not using an identity property
CREATE PROC Get_nextID
@.ID char(20),
@.nextID numeric(18) output
as
update NextIDs
set @.nextID = NextID = NextID + 1
where ID = @.ID
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||nonamehkg@.hotmail.com wrote:
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
>
You don't. You retrieve the IDENTITY value afterwards. Take a look at
the SCOPE_IDENTITY function in Books Online.
--
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
--|||To add the other responses, if you need to assign a known surrogate key
value before insertion, you need to use something other than identity. One
method is to use uniqueidentifier so that you can assign the value with
NEWID() or via application code. Another method is to create a table that
contains a row with the last (or next) assigned key value and use that table
to update and retrieve the key value prior to insertion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<nonamehkg@.hotmail.com> wrote in message
news:1141275178.710563.65560@.i40g2000cwc.googlegroups.com...
> Hi all, let say I have a table:
> CREATE TABLE test (
> [MyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) NOT NULL
> )
> How do I know the next value that MyID gonna have before inserting a
> new record?
> What is the best way to do that?
> Get the max number plus one? Wouldn't it be expensive to do it this
> way?
>
> Thanks
> --
> ky
>|||Thanks eveyone, I think SCOPE_IDENTITY is what I should use.
I actually need to know the id of a newly inserted record, and then
create a folder using that id as name.
It helps a lot thanks again.

Best way to force a varchar column to have no whitespace

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrencemetaperl wrote:

Quote:

Originally Posted by

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Use an update trigger to rewrite it with any whitespace stripped out.|||One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
']%')
WITH CHECK
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"metaperl" <metaperl@.gmail.comwrote in message
news:1159777716.594197.268970@.e3g2000cwe.googlegro ups.com...

Quote:

Originally Posted by

>I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.
>
Regards,
Terrence
>

|||On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:

Quote:

Originally Posted by

>One method is with a CHECK constraint that checks for the list of your
>prohibited characters. You may need to scrub data before adding the
>constraint.
>
>ALTER TABLE MyTable WITH CHECK
>ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
>']%')
WITH CHECK
>GO

Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP|||I think you wanted to include NOT there:

Yes, thanks for the catch, Hugo. I also had an extraneous NO CHECK and '^'
in the expression. The corrected version:

ALTER TABLE Table1 WITH CHECK
ADD CONSTRAINT CK_Table1_Col1
CHECK (Col1 NOT LIKE '%[' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) + ']%')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:hh33i2p3mo572838m7c7hiiqdj2q8ckber@.4ax.com...

Quote:

Originally Posted by

On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:
>

Quote:

Originally Posted by

>>One method is with a CHECK constraint that checks for the list of your
>>prohibited characters. You may need to scrub data before adding the
>>constraint.
>>
>>ALTER TABLE MyTable WITH CHECK
>>ADD CONSTRAINT CK_MyTable_MyColumn
> CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9)
>+
>>']%')
> WITH CHECK
>>GO

>
Hi Dan,
>
I think you wanted to include NOT there:
>
ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO
>
BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.
>
--
Hugo Kornelis, SQL Server MVP

|||CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.

BEST UDF to handle char or varchar to smalldatatime converstion pr

Hi Experts,
I am importing data from text files, data is about 800GB, yeah thats right.
in different file and in different varchar format. like 01/01/2004 jan 17,
200, mm/dd/yy. it had bad data and good data, but there are millions and
millions records, and it has all kind of problems any body can think of.
is there any site or way to get any function which handle these problems,
or if any of you ever had chance to see this kinda function and pass to me.
thanking you in advace for your help..
-PermoodYou can use ISDATE to determine if the specified string can be converted to
a SQL Server datetime datatype: Examples:
SELECT ISDATE('1/01/2004')
SELECT ISDATE('jan 17, 200')
SELECT ISDATE('mm/dd/yy')
SELECT ISDATE('01/01/04')
You might consider performing data type validation and conversion as part of
your ETL process before importing data into SQL Server. This will likely
perform better than using an intermediate SQL Server staging table when you
have a lot of bad data and large volumes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"-Permood" <Permood@.discussions.microsoft.com> wrote in message
news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> Hi Experts,
> I am importing data from text files, data is about 800GB, yeah thats
> right.
> in different file and in different varchar format. like 01/01/2004 jan 17,
> 200, mm/dd/yy. it had bad data and good data, but there are millions and
> millions records, and it has all kind of problems any body can think of.
> is there any site or way to get any function which handle these problems,
>
> or if any of you ever had chance to see this kinda function and pass to
> me.
>
> thanking you in advace for your help..
> -Permood
>|||Thanks Dan,
here is problem, i am very new in my company, some 'DEVELOPERS' don't want
to change their way of doing and they decided to use sql staging, and
transformationg. I dont' have much experience with SQL Server either. So I
wanted to see if some thing is already there, i means any function, which and
handle most date problems and then i and enhance it according to my needs.
but thank you for your time and suggestions.
"Dan Guzman" wrote:
> You can use ISDATE to determine if the specified string can be converted to
> a SQL Server datetime datatype: Examples:
> SELECT ISDATE('1/01/2004')
> SELECT ISDATE('jan 17, 200')
> SELECT ISDATE('mm/dd/yy')
> SELECT ISDATE('01/01/04')
> You might consider performing data type validation and conversion as part of
> your ETL process before importing data into SQL Server. This will likely
> perform better than using an intermediate SQL Server staging table when you
> have a lot of bad data and large volumes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "-Permood" <Permood@.discussions.microsoft.com> wrote in message
> news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> > Hi Experts,
> >
> > I am importing data from text files, data is about 800GB, yeah thats
> > right.
> > in different file and in different varchar format. like 01/01/2004 jan 17,
> > 200, mm/dd/yy. it had bad data and good data, but there are millions and
> > millions records, and it has all kind of problems any body can think of.
> >
> > is there any site or way to get any function which handle these problems,
> >
> >
> > or if any of you ever had chance to see this kinda function and pass to
> > me.
> >
> >
> > thanking you in advace for your help..
> > -Permood
> >
>
>

BEST UDF to handle char or varchar to smalldatatime converstion pr

Hi Experts,
I am importing data from text files, data is about 800GB, yeah thats right.
in different file and in different varchar format. like 01/01/2004 jan 17,
200, mm/dd/yy. it had bad data and good data, but there are millions and
millions records, and it has all kind of problems any body can think of.
is there any site or way to get any function which handle these problems,
or if any of you ever had chance to see this kinda function and pass to me.
thanking you in advace for your help..
-Permood
You can use ISDATE to determine if the specified string can be converted to
a SQL Server datetime datatype: Examples:
SELECT ISDATE('1/01/2004')
SELECT ISDATE('jan 17, 200')
SELECT ISDATE('mm/dd/yy')
SELECT ISDATE('01/01/04')
You might consider performing data type validation and conversion as part of
your ETL process before importing data into SQL Server. This will likely
perform better than using an intermediate SQL Server staging table when you
have a lot of bad data and large volumes.
Hope this helps.
Dan Guzman
SQL Server MVP
"-Permood" <Permood@.discussions.microsoft.com> wrote in message
news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> Hi Experts,
> I am importing data from text files, data is about 800GB, yeah thats
> right.
> in different file and in different varchar format. like 01/01/2004 jan 17,
> 200, mm/dd/yy. it had bad data and good data, but there are millions and
> millions records, and it has all kind of problems any body can think of.
> is there any site or way to get any function which handle these problems,
>
> or if any of you ever had chance to see this kinda function and pass to
> me.
>
> thanking you in advace for your help..
> -Permood
>