finding length of text field

I need to find the length of a text field.The len function doesn't work with
text fields.
Is there an alternative?
Thanks!DATALENGTH()
HP wrote:
> I need to find the length of a text field.The len function doesn't work wi
th
> text fields.
> Is there an alternative?
> Thanks!|||Look in the BOL for DATALENGTH
HTH, jens Suessmeyer.|||HP (HP@.discussions.microsoft.com) writes:
> I need to find the length of a text field.The len function doesn't work
> with text fields.
As Jens and Trey said there is datalength().
In case you are using ntext, beware that datalength() returns the length
in bytes, and includes trailing blanks. For instance:
SELECT len(N'August '), datalength(N'August ')
Returns (6, 18).
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|||Look in the BOL for DATALENGTH.
HTH, Jens Suessmeyer.

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 column use with syscomments

In SQL 2000, I know that "Display Dependencies" (and probably the
Sysdepends table) are not accurate.
Does the syscomments field for a view always contain the correct and
current view definition?
I need to find all uses of a given field across all views (there are about
150 views). Is looking in syscomments, or Information_Schema, going to be
reliable? Are there better ways? I have looked in Google and haven't
found anything yet. Still looking though...
Thanks.
David WalkerYou can refresh all your views and then use system views
information_schema.columns.
Example:
use northwind
go
declare @.ts sysname
declare @.tn sysname
declare @.sql nvarchar(4000)
declare views_cursor cursor local fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'view'
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
open views_cursor
while 1 = 1
begin
fetch next from views_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_refreshview ''' + quotename(@.ts) + N'.' +
quotename(@.tn) + ''''
exec sp_executesql @.sql
end
close views_cursor
deallocate views_cursor
declare @.cn sysname
set @.cn = 'OrderID'
select
*
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsView') = 1
and objectproperty(object_id(quotename(table
_schema) + N'.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_name = @.cn
order by
table_schema,
table_name,
ordinal_position
go
AMB
"DWalker" wrote:

> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker
>|||> Does the syscomments field for a view always contain the correct and
> current view definition?
AFAIK, yes. With one exception. If you use sp_rename to rename a view, the i
nfo in syscomments will
have the old name (in the CREATE VIEW part).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNGP09.phx.gbl...[co
lor=darkred]
> In SQL 2000, I know that "Display Dependencies" (and probably the
> Sysdepends table) are not accurate.
> Does the syscomments field for a view always contain the correct and
> current view definition?
> I need to find all uses of a given field across all views (there are about
> 150 views). Is looking in syscomments, or Information_Schema, going to be
> reliable? Are there better ways? I have looked in Google and haven't
> found anything yet. Still looking though...
> Thanks.
> David Walker[/color]|||And another one is that if you use "select * ..." then you will not find any
column name in the syscomments, but you will in the syscolumns. That is the
reason why after refreshing the view, I selected from
information_schema.columns and not from syscomments.
AMB
"Tibor Karaszi" wrote:

> AFAIK, yes. With one exception. If you use sp_rename to rename a view, the
info in syscomments will
> have the old name (in the CREATE VIEW part).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DWalker" <none@.none.com> wrote in message news:eAQxymoSFHA.1384@.TK2MSFTNG
P09.phx.gbl...
>
>|||"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:B0934023-3D8D-46DE-AA73-C27AC81C8F73@.microsoft.com:

> And another one is that if you use "select * ..." then you will not
> find any column name in the syscomments, but you will in the
> syscolumns. That is the reason why after refreshing the view, I
> selected from information_schema.columns and not from syscomments.
>
> AMB
>
Thanks to you both. I didn't know about refreshing the views. I'll
steal that code from you, AMB, and keep it in my database. There are
times when I want Display Dependencies in EM to give me the right
answer, and it looks like refreshing the views when I need this
information, is the way to go.
Thanks!
Davidsql

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:
>

Finding and removing invisible characters

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

Finding and removing invisible characters

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

Finding and removing invisible characters

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

Finding and Grouping Records off of a given field value

In the "tblEmailGroupLink" table...

I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.

In the "tblEmailAddress" table...

The same "Emailid" field has a corresponding "EmailAddress" field.

What needed is all the email addresses found in the "EmailAdddress" field of these records.

I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.

Thanks,

Bill

Something like this:

Code Snippet

SELECT ea.EmailAddress
FROM tblEmailAddress ea
JOIN tblEmailGroupLink eg
ON ea.EmailID = eg.EmailID
WHERE eg.UnSubscribed = 'True'

|||

Arnie,

Thank you very much, it works perfect.

Bill

find? instr? indexof?

is there a sql keyword for find or instr?
i have a field i wish to make into two and i need the position of a string "-" in the field so i can do a select right and copy that data to a new colmCHARINDEX
Returns the starting position of the specified expression in a character string.

Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )sql

Find website from email address

Hi,

I have 2 fields in a database: 'email' and 'website'.

In the majority of cases, the website field is not populated even though the
email address is. In 90% of cases, the website will be 'www.' followed by
whatever is after the '@.' symbol in the email address.

Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Thanks!Would anyone be able to help me with the SQL that would take whatever

Quote:

Originally Posted by

is after the '@.' sign in the email address, add it to 'www.' and
populate the 'website' field?

UPDATE yourtable
SET Site = SUBSTRING(yourtable.email, CHARINDEX('@.', yourtable.email) + 1,
LEN(yourtable.email) - CHARINDEX('@.', yourtable.email))

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Would anyone be able to help me with the SQL that would take whatever is

Quote:

Originally Posted by

after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Here's one method:

UPDATE dbo.MyTable
SET website =
CASE WHEN CHARINDEX('@.', email) 0 AND CHARINDEX('@.', email) + 1 <
LEN(email) THEN
'www.' + SUBSTRING(email, CHARINDEX('@.', email) + 1, 255)
ELSE
website
END
WHERE
website IS NULL OR
website = ''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

|||Cheers guys! Much appreciated :o)

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).
Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>
|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>> is this possible in T-SQL? (SQL Server 2000)
>> I want to find if the field is not null but empty (has no text in it).
>>
>>
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>sql

find the greater element in list

I have a table with 2 field Id and amount.

I want to select the maximum amount for all the Ids.

e.g.

IdAmount

1

23

1

47

2

23

250

37

The result set should be

1

47

2

50

3

7

Can any one give any sql query to

fetch the appropriate result?

select ID, max(Amount)

from your table

Group by id

|||

Did it work?

If so, can you mark answer as correct. thanks

sql

find string

I want to find if a data string is somewhere in a table but i dont
know the table of the database or the field. Who can i search
anywhere?

Thanks

Brainjkhttp://vyaskn.tripod.com/search_all..._all_tables.htm

--
David Portas
----
Please reply only to the newsgroup
--

Find similar strings in two tables

I have two tables in the same SQL database. Both have a similar numeric
field. One field has the same number as the other field but is prefixed
with various letters. I know how to use LIKE but won't know the partial
string I am looking for. I am trying to use LIKE '%' + Field A or
something that will do this.

Eg.

Table 1 Field A is 'A12345"
Table 2 Field B is '12345"

I want to find every record in Table 1 Field A that contains the exact
Field B data.

*** Sent via Developersdex http://www.developersdex.com ***Select FieldA from Table1 where
convert(numeric,right(FieldA,len(FieldA)-1)) in (Select FieldB from
Table2)

Madhivanan|||Hi Robert
It is much easier to help when given the right information.
Please post your ddl (i.e. your create table statement) so that people
can know the data exactly.
Also be more specifi: are records in table 1 always with one letter
forllowed by 5 digits? or can it be ABC12345DEF ? Just an example.

without this information the best I think I can do is:
select A from T1 where exists
(select B from T2 where A like '%'+B+'%')|||robert lassiter (rlassiter@.shaw.ca) writes:
> I have two tables in the same SQL database. Both have a similar numeric
> field. One field has the same number as the other field but is prefixed
> with various letters. I know how to use LIKE but won't know the partial
> string I am looking for. I am trying to use LIKE '%' + Field A or
> something that will do this.
> Eg.
> Table 1 Field A is 'A12345"
> Table 2 Field B is '12345"
> I want to find every record in Table 1 Field A that contains the exact
> Field B data.

Assuming the the numbers are always at the end, this could do:

SELECT * FROM tbl1 a
WHERE EXISTS (SELECT *
FROM tbl2 b
WHERE charindex(b.fieldb, a.fielda) =
len(a.fielda) - len(b.fieldb) + 1)

Note that this is untested.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Find records with same values in a field

I have a table of postcodes and I want to find out which towns have the same
postcodes
e.g.
Table has these values
Bournemouth B34
Brighton B24
Chelmsford B34
I want to write the query which returns me Bournemouth and Chelmsford (based
on the postcode being the same and the town being different).
Any ideas please.
ThxHi,
select pc.City, pc.PostalCode
from PostalCode pc
inner join PostalCode pc1
on pc1.PostalCode=pc.PostalCode
where pc1.city<>pc.city
"Billy" wrote:

> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||Thank you
"Billy" wrote:

> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||On Mon, 14 Feb 2005 01:41:11 -0800, Billy wrote:

>I have a table of postcodes and I want to find out which towns have the sam
e
>postcodes
(snip)
Hi Billy,
SELECT a.Postcode, a.Town, b.Town
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Postcode = a.Postcode
AND b.Town > a.Town
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

find percentage of a field

HOW can i get the percentage for each rank?
http://shahabedeen.europe.webmatrixhosting.net/pic/persentile04.gifOh goody, I love this kind of problem!SELECT 1e2 * a.rank / (SELECT Sum(b.rank) FROM tblpoll AS b) FROM tblpoll AS aThe problem is that you need to cooerce the type change before you start doing the math instead of after the math is complete. I'll let you work out the details!

-PatP|||Pat, will the optimizer be smart enough to only run the subquery once? Probably, but I'd phrase it this way to be sure:

SELECT rank/TotalVotes
from tblpoll,
(SELECT Sum(rank) as TotalVotes FROM tblpoll) subquery

"1e2"? 100, right?|||Pat, will the optimizer be smart enough to only run the subquery once? Probably, but I'd phrase it this way to be sure:

SELECT rank/TotalVotes
from tblpoll,
(SELECT Sum(rank) as TotalVotes FROM tblpoll) subquery

"1e2"? 100, right?As long as the subquery isn't correlated, the optimizer will only run it once.

Yes, 1e2 is a floating point 100. In this case, the type is very important because you have to force the type coersion to occur before the division, not after it if you want to retain the fractional portion of the result.

-PatP|||Thanks pat...|||Dear guy,I have a new problem:
http://shahabedeen.europe.webmatrixhosting.net/pic/persentile05.gif
As u can see the float point is not beautiful enough,How can I reduce the float points to 2 number? :rolleyes: :D|||Round() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_93z8.asp)

-PatP

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thx
One way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>
|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
David Portas
SQL Server MVP

find missing/deleted records?

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5
thxOne way I can think of is,
populate a number table with entries 1 to 50000
and query
SELECT SlNo FROM tblNumbers WHERE
SlNo NOT IN(SELECT SlNo FROM table1 UNION
SELECT SlNo FROM Table2)
--
Roji. P. Thomas
SQL Server Programmer
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message
news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is
in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Mansoor,
Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?
That said UNION and OUTER JOIN are useful in this case. eg:
create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)
insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )
Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL
Will return only 1 row, unless you have gaps
Regards
AJ
"Mansoor Azam" <mansoorb@.shoa.net> wrote in message news:2h5n48F8ei13U1@.uni-berlin.de...
> I have 2 tables say table1 and table2 with the same structure. Each record
> is identified by a field 'SerialNo'. Now there should be a total of 500000
> records in both tables with serialno from 1 to 500000. Either a record is in
> table1 or table2. I want to find records (or SerialNo's) that are in
> neither table (if deleted by accident etc). What would be the sql query?
> I'm using SQL 6.5
> thx
>|||Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000
--
David Portas
SQL Server MVP
--