Check if image column is empty

Currently I use the follow Select statement to test if a non-nullable image
column is empty and not null:
Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpty
Can any body show me a better way to check if a column (non-nullable column)
of image data type is empty.DataLength(ColName) will be zero (0) if it's empty...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Or, actually, just check if the column's value itself = '' (empty string)...
That should work. you don't need to attempt to extract the first character
and test that...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:380D392D-B82A-493E-AA22-C137F4599577@.microsoft.com...
> DataLength(ColName) will be zero (0) if it's empty...
> "krygim" wrote:
>
image
IsEmpty
column)|||I got the error message: "The text, ntext, and image data types cannot be
compared or sorted."
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> Or, actually, just check if the column's value itself = '' (empty
string)...
> That should work. you don't need to attempt to extract the first
character
> and test that...
>
> "krygim" wrote:
>
image
IsEmpty
column)|||Yes, you're right, DataLength() is the only other way...
"krygim" wrote:

> I got the error message: "The text, ntext, and image data types cannot be
> compared or sorted."
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> string)...
> character
> image
> IsEmpty
> column)
>
>

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004
You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
David Portas
SQL Server MVP

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
--
David Portas
SQL Server MVP
--

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
..
David Portas
SQL Server MVP
--sql

check fields in select statement

hi,

I am getting some fields back from a select statement, how do you check one of the fields and display a string depending on what it is? Is there something like an if statement you can use? for example

select

field1,
field2 /*how do you check to see what it is here and display something depending on what it is*/

from
record

I am trying to see if field2 is a '' or empty string character

thxYou use a CASE statement.

SELECT field1, CASE field2 WHEN '' THEN 'BLANK' ELSE field2 END as Field2 FROM Table

If rather than '' the value might be null, you can use ISNULL

SELECT field1, ISNULL(field2,'BLANK') as field2 FROM Table

|||thank you for the help

Check constraint and foreign key error

The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-Unfortunately there's no system variable that gives you the actual error
message, so that you could parse it for more information. But from a client
application, you should be able to access the error message. For example,
Err.Description in ADO.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-|||I have the error message, but I want to format and translate it. And I didn'
t
know what kind of values can have the messages parameters. I have diferent
messages and todo's in case of check constraint and diferent in case of
foreign keys. And I didn't know what kind of any errors can appear with this
error number.
thanks
-enci-
"Narayana Vyas Kondreddi" wrote:

> Unfortunately there's no system variable that gives you the actual error
> message, so that you could parse it for more information. But from a clien
t
> application, you should be able to access the error message. For example,
> Err.Description in ADO.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
> The error message for err. 547 is:
> "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
> occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
> This error is raised when a foreign key error is occured, and when a check
> constraint error occured, too.
> What kind of values can have the parameters of this error message? How can
I
> know, what kind of error is this?
> thanks
> -enci-
>
>

Check an user is the owner of a table

Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA
|||You could get the currently logged in user's name using:
SELECT USER_NAME()
The owner of an object can be determined as shown below:
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('sysobjects'), 'OwnerID'))
In the above example, I used sysobjects as the table name.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat
|||Just a note: SUSER_SNAME() returns the login name, and that doesn't
necessarily have to match the user name - but objects are owned by the user
names.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uR2I1CTYFHA.2884@.tk2msftngp13.phx.gbl...
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA
|||You could just check the catalogue:
SELECT CASE WHEN EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOGUE = 'SearchDB' --Replace the desired database
name here.
AND TABLE_SCHEMA = USER_NAME()
AND TABLE_NAME = 'SearchTable' --Replace the desired table
name here.
)
THEN 'True'
ELSE 'False'
END
Sincerely,
Anthony Thomas

"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat

Check an user is the owner of a table

Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
--
Venkat"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA|||You could get the currently logged in user's name using:
SELECT USER_NAME()
The owner of an object can be determined as shown below:
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('sysobjects'), 'OwnerID'))
In the above example, I used sysobjects as the table name.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
--
Venkat|||Just a note: SUSER_SNAME() returns the login name, and that doesn't
necessarily have to match the user name - but objects are owned by the user
names.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uR2I1CTYFHA.2884@.tk2msftngp13.phx.gbl...
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA|||You could just check the catalogue:
SELECT CASE WHEN EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOGUE = 'SearchDB' --Replace the desired database
name here.
AND TABLE_SCHEMA = USER_NAME()
AND TABLE_NAME = 'SearchTable' --Replace the desired table
name here.
)
THEN 'True'
ELSE 'False'
END
Sincerely,
Anthony Thomas
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
--
Venkat

Check an user is the owner of a table

Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA|||You could get the currently logged in user's name using:
SELECT USER_NAME()
The owner of an object can be determined as shown below:
SELECT USER_NAME(OBJECTPROPERTY(OBJECT_ID('syso
bjects'), 'OwnerID'))
In the above example, I used sysobjects as the table name.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat|||Just a note: SUSER_SNAME() returns the login name, and that doesn't
necessarily have to match the user name - but objects are owned by the user
names.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uR2I1CTYFHA.2884@.tk2msftngp13.phx.gbl...
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any way to check that the logged in user is the owner of a
> given table? Can any one give me the T-Sql statement for this.
> --
> Venkat
>
DECLARE @.Login sysname
SET @.Login = SUSER_SNAME()
SELECT
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = '<databasename>'
AND TABLE_SCHEMA = @.Login
AND TABLE_NAME = '<tablename>'
Rick Sawtell
MCT, MCSD, MCDBA|||You could just check the catalogue:
SELECT CASE WHEN EXISTS(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOGUE = 'SearchDB' --Replace the desired database
name here.
AND TABLE_SCHEMA = USER_NAME()
AND TABLE_NAME = 'SearchTable' --Replace the desired table
name here.
)
THEN 'True'
ELSE 'False'
END
Sincerely,
Anthony Thomas
"Venkat" <tammana@.inooga.com> wrote in message
news:esmSq4SYFHA.612@.TK2MSFTNGP12.phx.gbl...
Hi,
Is there any way to check that the logged in user is the owner of a
given table? Can any one give me the T-Sql statement for this.
Venkat

Check 3 occurrences of double characters.

Hi all,

I need to write some sort of statement to identify which numbers from a list fall into the following pattern:

% TwoIdenticalNumbers % TwoIdenticalNumbers % TwoIdenticalNumbers %

for example:

08812355677

I thought I would be able to use a LIKE statement but I'm not sure how to write it so that double characters are used rather than single. For example if I wanted to check three numbers appear within the string I could do the following:

SELECT *

FROM Table

WHERE Number LIKE '%[0-9]%[0-9]%[0-9]%'

I think the easiest way to check the doubles would be to represent them as a string, so I want to replace each of the [0-9] above with something like the following:

['00' OR '11' OR '22' OR '33' OR '44' OR '55' OR '66' OR '66' OR '77' OR '88' OR '99']

How could I write this using proper SQL code?

Any help would be much appreciated.

Thanks very much,

Will

Thre is no predefined expressions available,

Following approach is one of the way to achive this,

Code Snippet

Create Table #data (

[Numbers] Varchar(100)

);

Insert Into #data Values('1242432');

Insert Into #data Values('242423423');

Insert Into #data Values('2332232');

Insert Into #data Values('828289');

Insert Into #data Values('99887766');

Insert Into #data Values('92829299');

select

numbers

from #data

cross join

(

select '00' n

union all

select '11'

union all

select '22'

union all

select '33'

union all

select '44'

union all

select '55'

union all

select '66'

union all

select '77'

union all

select '88'

union all

select '99' ) as d

group by numbers having sum(case when patindex('%'+ n + '%',numbers) <> 0 Then 1 Else 0 End) >= 3

|||

:-) Looks oofy, but works

SELECT * FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

|||

The idea is good, but the following query might fit the asked requirement,(3 occurrence of …)

Code Snippet

SELECT Id FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%X%X%'

|||

Very close. At least three instances are needed.

'11' -- does not qualify

'1122' -- does not qualify

'112233' -- bingo

...

SELECT

*

FROM

(select '11' as ID) as t

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

AMB

|||

Talking about ugly. Try:

-- thanks to Manni for the sample data

select

numbers

from

#Data

where

(len(numbers) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(numbers, '00', ''), '11', ''), '22', ''), '33', ''), '44', ''), '55', ''), '66', ''), '77', ''), '88', ''), '99', ''))) / 2 >= 3

AMB

|||Thanks for all the help, this has saved me a lot of time trying to figure this one out.

CHARINDEX in CASE Within SELECT Statement

Hello.
I need help with using CHARINDEX.
I have a column in a table (Discount_Specification) that could hold the
following values:
LF(I03U,CHA-14,ALL-0)
MR(I05U,I06U,CHA-5)
etc.
I'm inserting into another table and need to pick up the value following
"CHA-" in that column.
I've created a User Defined Function to do this but would like to make the
SQL code more efficient. The UDF has an argument which specifies which value
to pick up (separated by the delimiter). In example 1 it's the 2nd value. In
example 2 it's the 3rd value.
My SELECT code looks something like this:
select provider_id, last_name, first_name,
CASE
WHEN CHARINDEX(@.CHA,SM.Discount_Specification) > 0 THEN
CASE
WHEN Left(SM.Discount_Specification,2) IN(@.LF, @.LS, @.PF, @.PL) THEN
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue(We
bCentral.dbo.udf_GetNthTextValue
(SubString(SM. Discount_Specification,4,DataLength(RTri
m(SM.Discount_Specific
ation))-4),@.Comma,2),@.Dash,2),@.Comma,1))
ELSE
CASE
WHEN Left(SM.Discount_Specification,3) = @.CHA THEN
-- Charge is all by itself
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue
(RTrim(SM. Discount_Specification),@.Dash,2),@.Comma,
1))
ELSE 1
END
END
I would like to use a variable instead.
Something like:
DECLARE @.Pos SmallInt
SELECT provider_id, last_name, first_name,
@.Pos = CHARINDEX(@.CHA,SM.Discount_Specification)
CASE
WHEN @.POS > 0 THEN
webcentral.dbo.udf_ConvertDecimalAllowance etc. etc.
END
I get an error on the line where I'm setting @.Pos and I don't know what
syntax to use.
Any suggestions will be greatly appreciated.
Thanks,
RitaYou cannot set variables and return results to the client in the same SELECT
statement.
I'm not sure what it really is that you're trying to achieve, but I think
this function might help you parse those strings:
create function dbo.fnParse
(
@.charValue varchar(1000)
,@.findThis varchar(1000) = null
)
returns int
as
begin
declare @.result int
set @.findThis = isnull(@.findThis, 'CHA-')
select @.charValue
= substring(@.charValue, charindex(@.findThis, @.charValue) +
len(@.findThis), len(@.charValue))
select @.charValue
= substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
select @.result
= case
when isnumeric(@.charValue) = 1
then cast(@.charValue as int)
else null
end
return @.result
end
go
Use like this:
select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
ML
http://milambda.blogspot.com/|||Thanks so much for your response.
That's exactly what I'm playing around with - creating a UDF that uses the
CHARINDEX. Your example helps a lot!
Rita
"ML" wrote:

> You cannot set variables and return results to the client in the same SELE
CT
> statement.
> I'm not sure what it really is that you're trying to achieve, but I think
> this function might help you parse those strings:
> create function dbo.fnParse
> (
> @.charValue varchar(1000)
> ,@.findThis varchar(1000) = null
> )
> returns int
> as
> begin
> declare @.result int
> set @.findThis = isnull(@.findThis, 'CHA-')
> select @.charValue
> = substring(@.charValue, charindex(@.findThis, @.charValue) +
> len(@.findThis), len(@.charValue))
> select @.charValue
> = substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
> select @.result
> = case
> when isnumeric(@.charValue) = 1
> then cast(@.charValue as int)
> else null
> end
> return @.result
> end
> go
> Use like this:
> select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
> ,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
>
> ML
> --
> http://milambda.blogspot.com/|||Gee, that sounds like a good deed from me. Hope Santa reads this newsgroup.
:)
At least one of the elves should. Or is Santa not using SQL...?
Anyway, just remember that this *is* the newsgroup with solutions. :)
ML
http://milambda.blogspot.com/|||>> Any suggestions will be greatly appreciated. <<
1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Learn to normalize your schema and stop writing COBOL-style string
manipulations in procedures. You will waste an insane amount of time
and spce doing this.
I also watched code like this kill some children in Africa. The
programmer had used strings hold the package size quantity for drugs.
When the drug suppliers agreed to provide smaller packages (i.e.
quantity one), the string was changed, but not the front end. The
result was when you thought you had ordered a 5-unit package, you got
a 1-unit package instead.
My guess is that you need a table of Discounts (notice the plural name
to show it is a set) with the amount, the source and the code for each
of the discounts. You then do a simple join and get rid of all that
"pseudo-COBOL" field extractions.|||Ouch!
I think I should have posted everything here pertaining to my question so
there would be no misunderstanding.
I have no control of the data coming in. We are supplied this by our clients
and then we have to import the non standard data into our standard SQL table
s.
No matter what, I can't get passed having to parse out bits of information
all strung
together within 1 column separated by a delimiter.
"--CELKO--" wrote:

> 1) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> 2) Learn to normalize your schema and stop writing COBOL-style string
> manipulations in procedures. You will waste an insane amount of time
> and spce doing this.
> I also watched code like this kill some children in Africa. The
> programmer had used strings hold the package size quantity for drugs.
> When the drug suppliers agreed to provide smaller packages (i.e.
> quantity one), the string was changed, but not the front end. The
> result was when you thought you had ordered a 5-unit package, you got
> a 1-unit package instead.
> My guess is that you need a table of Discounts (notice the plural name
> to show it is a set) with the amount, the source and the code for each
> of the discounts. You then do a simple join and get rid of all that
> "pseudo-COBOL" field extractions.
>|||>> have no control of the data coming in. We are supplied this by our clien
ts and then we have to import the non standard data into our standard SQL ta
bles. No matter what, I can't get passed having to parse out bits of informa
tion all strung together wi
thin 1 column separated by a delimiter. <<
Just because the source data is a mess, you are not required to
propagate it in the schema. Parse it at load time and edit everything.
Have you looked into an ETL tool of some kind? You might be able to
write something in a small, fast scripting language likie AWK, Perl,
etc.|||Hmm.
I never thought to do that. The input files are always imported "as is" into
SQL staging tables using DTS packages. The reason being if there is ever a
question regarding the output data we have the original data in SQL format
against which queries can be run.
I use an ActiveX script to popultae the columns so that may be where I could
parse out the values. I've used AWK and Perl sparingly in the past. There is
a lot of logic going on when I get the value out of the string as to where t
o
place it so I think ActiveX is the best way to go.
I also need to consider speed since some of the files have millions of rows
in them.
Would it be faster to parse within the DTS ActiveX script or the Stored
Procedure (which I'm currently doing)? I heard that Stored Procedures are
much faster than DTS packages.
Thanks for the suggestion.
"--CELKO--" wrote:

within 1 column separated by a delimiter. <<
> Just because the source data is a mess, you are not required to
> propagate it in the schema. Parse it at load time and edit everything.
> Have you looked into an ETL tool of some kind? You might be able to
> write something in a small, fast scripting language likie AWK, Perl,
> etc.
>|||A set-based solution will be more efficient if run on the server. But that
will require accessing the source files through a linked server. So, the
question of the day is - what type are the source files?
ML
http://milambda.blogspot.com/|||They're fixed length text files.
"ML" wrote:

> A set-based solution will be more efficient if run on the server. But that
> will require accessing the source files through a linked server. So, the
> question of the day is - what type are the source files?
>
> ML
> --
> http://milambda.blogspot.com/

Character to date conversion

I am trying to write and insert statement that takes up a date value, such as,
Insert into tablename(date_column) values (date('some date', format));

I however do not know how exactly to convert a character string to transact sql date. can someone please lead me in correct direction? Some documentation could also help.

Thanks
--Shilpa

No special functions needed. Just insert it as text:

CREATE TABLE test
(
dateValue datetime
)
go
INSERT INTO test
VALUES ('2006-01-01T00:00:00')
go

As for format of datetime values, there are many different ways to format a date, but really only a couple of good ways. Look up "Date Data Types" in books online. It explains it really well.

|||I am new to MS domain. Couldn't get to Books online.
I attempted your suggested Insert statement. The problem is, my date format is different,
it appears as a 12 hour clock. The date shows as 05/12/2005 06:30:12 PM. The table design view shows it as general date. Could you suggest a format for this?

Thanks
S
|||INSERT INTO test
VALUES ('2006-01-01T00:00:00')

Sorry about the previous post. Your solution works without the T before time insertion.
Thanks,
S
|||

There is a copy of books online (not unsurprisingly) online:

http://msdn2.microsoft.com/en-us/ms130214(sql.90).aspx

You can also download it online. They update it quarterly, and this download is from April:

http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en

changinh DB of stored procedure

I have a stored procedure that as one of parameters receiving the database.
I need to change the database that SQL statement in store procedure working
on according to parameter.
I cant find any solution except fill naming and rewiring whole statement
depend on parameter or building SQL statement as string and then running it
in store procedure.
I know what there are USE { database } statement in TSQL , but it's doesn't
work in stored procedure.
Please, help if you have any solution of this problem.
Thank you, David Potahinsky.David,
You can't use a parameter after USE, but you can do something like
this, I believe, if you know the names of all the databases that might
be chosen:
if @.db = 'somedatabase'
use somedatabase
else if @.db = 'differentdatabase'
use differentdatabase
else if ...
If you wish, you can also return silently or throw an error if the value
passed for @.db is not one you've taken care of.
SK
David Potahisnsky wrote:

quote:

---

>I have a stored procedure that as one of parameters receiving the database.
>I need to change the database that SQL statement in store procedure working
>on according to parameter.
>I cant find any solution except fill naming and rewiring whole statement
>depend on parameter or building SQL statement as string and then running it
>in store procedure.
>
>I know what there are USE { database } statement in TSQL , but it's doesn't
>work in stored procedure.
>
>Please, help if you have any solution of this problem.
>
>Thank you, David Potahinsky.
>
>
>

---

|||David Potahisnsky (david.potashinsky@.kabbalah.com) writes:

quote:

---

> I have a stored procedure that as one of parameters receiving the
> database. I need to change the database that SQL statement in store
> procedure working on according to parameter.
> I cant find any solution except fill naming and rewiring whole statement
> depend on parameter or building SQL statement as string and then running
> it in store procedure.

---

Put the stored procedure with the main logic in all databases. In the
master you can say:
DECLARE @.logic_sp sysname
SELECT @.logic_sp = @.db + '.dbo.your_sp'
EXEC @.err = @.logic_sp @.par1, @.par2, ...
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is there a way to avoid creation of same stored procedures for each database
as it makes maintenance a hell, but to have for ex. REPORTS database with
all stored procedures that can access tables in other DBs on demand by
parameter?
Something that is logically equivalent to:
CREATE PROCEDURE REPORT_TEST1 (@.DB VARCHAR(100)) AS
BEGIN
USE @.DB
SELECT * FROM TABLE1
-- should get records from @.db.dbo.table1
...
END
But the problem is that T-SQL doesn't allow USE in PROCEDURE.
I'm aware that use of dynamic queries in style EXEC ('SELECT * FROM
'+@.DB+'.dbo.table1') is possible but barely acceptable again because of
maintenance pains.
Any other alternative solutions?
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns945BF1298BDD1Yazorman@.127.0.0.1...

quote:

---

> David Potahisnsky (david.potashinsky@.kabbalah.com) writes:
> Put the stored procedure with the main logic in all databases. In the
> master you can say:
> DECLARE @.logic_sp sysname
> SELECT @.logic_sp = @.db + '.dbo.your_sp'
> EXEC @.err = @.logic_sp @.par1, @.par2, ...
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

---

|||Myrth (m0sh3_NO@.SPAM_hotmail.com) writes:

quote:

---

> Is there a way to avoid creation of same stored procedures for each
> database as it makes maintenance a hell, but to have for ex. REPORTS
> database with all stored procedures that can access tables in other DBs
> on demand by parameter?

---

Actually there is: this is the way the system stored procedure works.
However, while you can add your own system stored procedures, this is
deprecated and unsupported, and also has security issues.
And anyway the scheme breaks down the day the databases are spread over
more than one server. In this case, you have no choice than to deploy
your procedure on at least every server.
So the real answer to your problem is to devise a distribution
mechanism, so that you easily can distribute updates to your code to
your databases, no matter they are all on the same server, or spread
out on several customer sites. Obviously for this you need a master
to deliver from, but this master database is not an SQL database -
it's a version control system (which may use SQL Server as its storage
engine, but that's another story).
Such a distribution can be assembled in different ways. The heart of the
procedure is always that in your version-control system you set a
baseline. The terminology changes between version-control systems,
but often you talk about "labels". Then you assemble the changes between
two labels. This can be performed by manually building a BAT-fil which
uses OSQL to install the stored procedures, or it can be more elaborate.
There are several third-party tools on the market. Popular is SQL Compare
from Red Gate, www.red-gate.com, although it works from a different
perspective than I've outlined here. Myself, I have made the tool we
use in our shop available as freeware, see http://www.abaris.se/abaperls/.
Thus, what you have is a configuration-management problem, not one of
writing stored procedures. It may seem inflexible that stored procedures
are tied to one certain database, but there are some good reasons for
this. The most important is that although the logic in the stored
procedure may be the same in two databases, the query plan that SQL Server
should use to retrieve the data may not, because the data is different
in the two databases.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||we use views successfully here. write your stored procedures to access views
instead of base tables. upon deployment you can literally generate the cross
database views from a table in your main db (table with metadata). an sp can
produce multiple of these:
IF OBJECT_ID('v_<foreign_db>_<base_table_name>') IS NOT NULL DROP VIEW
v_<foreign_db>_<base_table_name>
GO
CREATE VIEW v_<foreign_db>_<base_table_name>
AS
SELECT *
FROM <foreign_db>.dbo.<base_table_name>
GO
where <foreign_db> is a logical name of a db in your system e.g. "rep" for
reporting (not the actual deployment specific sql db name) and can be passed
as a paramter to the sp or even better -- can be persisted in a setup table.
of course using * for column_list is not "production" enough so you might
want to store view's column lists in you "metadata cross-db view definition"
table as well.
we use multiple prefixes for views names like this:
v_<host_db>_<foreign_db>_<foreign_view_name> to allow different column list
for each <host_db> that is each sub-system of the solution. so we end up
with several views pointing to a single base table (but from different
sub-systems).
cheers,
</wqw>
"Myrth" <m0sh3_NO@.SPAM_hotmail.com> wrote in message
news:epkBLBnyDHA.2032@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Is there a way to avoid creation of same stored procedures for each

---

database

quote:

---

> as it makes maintenance a hell, but to have for ex. REPORTS database with
> all stored procedures that can access tables in other DBs on demand by
> parameter?
> Something that is logically equivalent to:
> CREATE PROCEDURE REPORT_TEST1 (@.DB VARCHAR(100)) AS
> BEGIN
> USE @.DB
> SELECT * FROM TABLE1
> -- should get records from @.db.dbo.table1
> ...
> END
> But the problem is that T-SQL doesn't allow USE in PROCEDURE.
> I'm aware that use of dynamic queries in style EXEC ('SELECT * FROM
> '+@.DB+'.dbo.table1') is possible but barely acceptable again because of
> maintenance pains.
> Any other alternative solutions?
>
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns945BF1298BDD1Yazorman@.127.0.0.1...
statement[QUOTE]
running[QUOTE]
>

---