Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

convert a table into tree

Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.

suppose table is like this. username parentid downline

B A left

C A right

D B left

E B right...

I have to also determine the level in the tree...please help...

CREATETABLE [dbo].[#tree2](

[username] [char](1),

[parentid] [char](1),

[downline] [nvarchar](50),

[id] [int]NOTNULL

)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('b','a','left',1)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('c','a','right',2)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('d','b','left',3)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('e','b','right',4)

INSERTINTO [#tree2]([username],[parentid],[downline],[id])VALUES('a',NULL,'right',5)

;WITH myCTE(levels, parentid, username, tree, downline)

AS

(SELECT 1, parentid, username,CAST(usernameasvarchar(50)),downline

FROM #tree2

WHERE parentidisNULL

UNIONALL

SELECT t1.levels+1,

t2.parentid,

t2.username,

CAST(tree+'/'+ t2.usernameASvarchar(50)),

t2.downline

FROM myCTE t1JOIN #tree2 t2ON t1.username=t2.parentid

WHERE t1.levels<10--you can change this up to the level you want

)

SELECT levels, parentid, username, downline, treeFROM myCTE

ORDERBY levels

DROPTABLE #tree2

convert a datatype

i have table tt.
it contains two fields one is ttno int,doj datetime . i want to
convert to datetime to varchar .
how it is ... give me some examplesNo problem, look at the CONVERT function for a specific format you want
to extract. I prefer using the ISO one, though its good for ordering
and international.

CONVERT(VARCHAR(10),GETDATE(),112)

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Check the topic CONVERT in SQL Server Books Online.

--
Anith|||Anith Sen (anith@.bizdatasolutions.com) writes:
> Check the topic CONVERT in SQL Server Books Online.

Actually, the topic is CAST and CONVERT, lest someone looks in the wrong
place and don't find it.

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

convert ###,###,##0 to INT

Hi,

I've imported an Excel file into a work table, via an Access Project. One of my fields is an integer, represented in Excel with thousands separator e.g. 3,137,458

The above now sits in a varchar column, and I need to convert these values to an INT. Strangely, is numeric() returns One, but then convert( int, ...) does not like the commas.

To add insult to injury, my MSDE does not seem to allow me to CREATE FUNCTION. It protests even if I do Grant Create Function to Login, while running as 'sa'. Side question: is this a known limitation of MSDE ?

Is there an efficient way to convert such strings to Int ?

I note that the commas may actually be missing, since their presence depends on the "Digit Grouping" value in the Regional Settings of Control Panel.

In the past, I was using Sybase, and I had to use set-based queries, running against a few work fields in my table. The first query would use charindex() to find the position of the first comma, if any. The second query would pick up the portion of the string up to the comma, then another query chasing the next comma, etc. Rather painful.Hmm...maybe you could try playing around with the replace command to filter out the commas.

I tested this 1 line code in QA and it works fine.

select cast(replace('3,137,458',',','') as int).|||oops, temporary blindness ... apologies ... please ignore this question

convert( int, REPLACE( column_name, ',', '' ) )|||thanks, mate, I've just found it at the same time. Works like a charm.
Me self-learner too...

Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

Controlling Updated fields on trigger

In myTable i've got a trigger for INSERT, UPDATE, DELETE
I Update myField1 of myTable in the trigger but not in the query that starts
the trigger.
Then i check if UPDATE(myField1). This is true or false?
CREATE TRIGGER tr_MyTable ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
...
UPDATE myTable SET myField1 = 'XYZ' WHERE ...
...
if update(myField1 ) /* IS TRUE OR FALSE ' */
begin
..
endFalse.
The UPDATE( ) clause comes from the state of the data that fired the
trigger, not from any manipulations inside the trigger.
Also, FWIW, if your update sets a column to the same value it had before the
update, that will also be considered an updated column. (Since I did not
find the clause useful, I stopped using it. If it is smarter in 2000,
someone should know.)
RLF
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:9B1547D0-05DA-438B-947D-8DFDA29FE77A@.microsoft.com...
> In myTable i've got a trigger for INSERT, UPDATE, DELETE
> I Update myField1 of myTable in the trigger but not in the query that
> starts
> the trigger.
> Then i check if UPDATE(myField1). This is true or false?
> CREATE TRIGGER tr_MyTable ON dbo.MyTable
> FOR INSERT, UPDATE, DELETE
> AS
> ...
> UPDATE myTable SET myField1 = 'XYZ' WHERE ...
> ...
> if update(myField1 ) /* IS TRUE OR FALSE ' */
> begin
> ...
> end
>|||Russell Fields (RussellFields@.NoMailPlease.Com) writes:
> Also, FWIW, if your update sets a column to the same value it had before
> the update, that will also be considered an updated column. (Since I
> did not find the clause useful, I stopped using it. If it is smarter in
> 2000, someone should know.)
I have not tried it, but there is no reason to expect it to be smart.
If you update 1000 rows, and one of them changes value, what should IF
UDPATE return?
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

Controlling Reporting Services Export

Hello,
I have a CSV export that needs some fields qualified by double quotes and
some that do not. How do I control this behavior in Reporting Services?
I have tried specifying a blank text qualifier in rsreportserver.config and
surrounding the applicable fields by double quotes but that doesn't work as
the double quote qualifier gets repeated.
TIA,
Ray
SS2K5On Jan 7, 4:06 pm, raybouk <rayb...@.discussions.microsoft.com> wrote:
> Hello,
> I have a CSV export that needs some fields qualified by double quotes and
> some that do not. How do I control this behavior in Reporting Services?
> I have tried specifying a blank text qualifier in rsreportserver.config and
> surrounding the applicable fields by double quotes but that doesn't work as
> the double quote qualifier gets repeated.
> TIA,
> Ray
> SS2K5
The quickest way to accommodate this is to use casting in SSRS (i.e.,
CStr(Fields!SomeFieldName.Value)). You would cast the fields that you
need to have quotes around. Also, you could use the format part of the
Properties tab for the fields you need to have the quotes around. An
expression similar to this might work: ="''#''" Another alternative
(more reliable, though more work) would be to use a StreamReader and
StreamWriter after the fact (after exporting the report to a given
format) to read in the report file into a string or stringbuilder,
then use String.Replace() (or String.Format()) and then output the
file with the quote identifiers for certain fields. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

Control the size of a text box via a report parameter

We have some note fields that are very large so reports take up too many
pages.
However we would like to either limit the field to lets say 4 lines or all
lines via some parameter.
Is there any way to do this?Did you look into using the Left() function to limit the field content to a
certain length based on a report parameter? You could use an expression
similar to this for the textbox value property:
=iif(Parameters!RestrictLength.Value = True,
Left(Fields!LongDescription.Value, 200), Fields!LongDescription.Value)
MSDN documentation for Left():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctLeft.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:eMmW3XeFFHA.3272@.TK2MSFTNGP10.phx.gbl...
> We have some note fields that are very large so reports take up too many
> pages.
> However we would like to either limit the field to lets say 4 lines or all
> lines via some parameter.
> Is there any way to do this?
>

Control Source field as value list

Hi!
I have a strored proc which adds up fields separated by semicolon into a new one and I'd like to use it as a combo box's control source field as a Value list. But it's just insert the whole field into one line like value1;value2;value3 .
Is there any way to get these values selectable in the combo box?

Thanks in advance for any help.

VillWhat interface are you using. Access? You should post this in the appropriate forum, as it is not a SQL Server issue.

Control of Parameter Field Layout

Greetings

Is it possible to control how the Parameter fields are displayed on report at the point of selection? At the moment they are equally dropped on the form in two columns. I would like to place the Alpha Numerical ones at the top and then organise the date parameters underneath in some kind of logical order.

Regards

The parameter layout is not configurable. But you can order the parameters in your report to get two columns in a more logical order.

|||

Oh well that is a shame.

Thank you anyway

Regards

Control names of fields when exporting to CSV

My query returns fields that have spaces in the names, for example:

select o.OrderID as 'INVOICE NUMBER', etc..

When I created the DataSet for this query in report designer, it changed all spaces in the field names to underscores, i.e. INVOICE_NUMBER.

This report must be exported to CSV, and the names of the fields (first row in csv) must have the spaces, not underscores. But it appears RS uses the field names (not the names in the column headers of the report table control) as the field names. Is there any way for me to force the field names in the first row of the csv to something other than the field names in the DataSet?

Thanks!

I think I you should be able to rename the column headers to change the results in the CSV file.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

ALFKI,

You can go to the column properties data output tab and enter the "Element Name" and select output as "Yes" this is how I can my export to csv files.

Ham

|||

should read

"how I change my export column names in the csv files"

|||Thanks - the only problem is, Element Name cannot contain a space , hence the underscores. Oh, well...|||

ALFKI,

Sorry, Yes, you are still bounded by the textbox property field names. You could not for instance name a textbox "My Textbox Value" is must be My_TextBox_Value. I mistaken thought you were just trying to rename the field.

Ham

contents of image fields

Hi,

I have a SELECT FROM TABLE query and in that table there is a field of type
image. Result of this select goes throug internet do its destination. But in
fact I need only to know if in this field is or not an image. Is there any
funciotn which gives me information about contents of image fields?

Regards, PaulIs there any funciotn which gives me information about contents of image

Quote:

Originally Posted by

fields?

The only thing SQL Server knows about image column contents is the
DATALENGTH. It is up to the application to interpret the contents. If you
store data of different types in the same column (not a good design, IMHO),
you'll need another column to indicate the type.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Koral" <koral00@.op.plwrote in message news:f7vir2$nur$1@.news.onet.pl...

Quote:

Originally Posted by

Hi,
>
I have a SELECT FROM TABLE query and in that table there is a field of
type image. Result of this select goes throug internet do its destination.
But in fact I need only to know if in this field is or not an image. Is
there any funciotn which gives me information about contents of image
fields?
>
Regards, Paul
>

CONTAINS in SQL Server

Hi,
I have a question about MS SQL Server 2005:
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
Or there is a more compact way to write this query.
Thank you very much for your answer,
Regard,
Djamila.
that's one way. If you don't care which column the hit is found in you could
do this.
Select f1, ..., fm From table
Where CONTAINS(*, expr)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<djamilabouzid@.gmail.com> wrote in message
news:1159835993.016620.306290@.h48g2000cwc.googlegr oups.com...
> Hi,
> I have a question about MS SQL Server 2005:
> I have a table in the database that contains 70 fields. I must perform
> full-text search in about 60 fields. I use for that the full-text
> searching "CONTAINS" like that:
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
> My question is: for the 60 fields in the table that I want to do
> full-text search for the same expression, may I write a query like
> that:
> Select f1, ..., fm
> From table
> Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
> CONTAINS(f60,expr)
> Or there is a more compact way to write this query.
> Thank you very much for your answer,
> Regard,
> Djamila.
>

CONTAINS in SQL Server

Hi,

I have a question about MS SQL Server 2005:

I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:

SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')

My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:

Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)

Or there is a more compact way to write this query.

Thank you very much for your answer,

Regard,

Djamila.FROM BOL,

column_list
Indicates that several columns, separated by a comma, can be specified.
column_list must be enclosed in parentheses. Unless language_term is
specified, the language of all columns of column_list must be the same.

*
Specifies that all columns in the table registered for full-text
searching should be used to search for the given contains search
condition. The columns in the CONTAINS clause must come from a single
table. If more than one table is in the FROM clause, * must be
qualified by the table name. Unless language_term is specified, the
language of all columns of the table must be the same.

We can use * for all full-text Columns or Column List

M A Srinivas

djamilabouzid@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I have a question about MS SQL Server 2005:
>
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
>
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
>
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
>
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
>
Or there is a more compact way to write this query.
>
Thank you very much for your answer,
>
Regard,
>
Djamila.

CONTAINS in SQL Server

Hi,
I have a question about MS SQL Server 2005:
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
Or there is a more compact way to write this query.
Thank you very much for your answer,
Regard,
Djamila.that's one way. If you don't care which column the hit is found in you could
do this.
Select f1, ..., fm From table
Where CONTAINS(*, expr)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<djamilabouzid@.gmail.com> wrote in message
news:1159835993.016620.306290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a question about MS SQL Server 2005:
> I have a table in the database that contains 70 fields. I must perform
> full-text search in about 60 fields. I use for that the full-text
> searching "CONTAINS" like that:
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
> My question is: for the 60 fields in the table that I want to do
> full-text search for the same expression, may I write a query like
> that:
> Select f1, ..., fm
> From table
> Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
> CONTAINS(f60,expr)
> Or there is a more compact way to write this query.
> Thank you very much for your answer,
> Regard,
> Djamila.
>

CONTAINS in SQL Server

Hi,
I have a question about MS SQL Server 2005:
I have a table in the database that contains 70 fields. I must perform
full-text search in about 60 fields. I use for that the full-text
searching "CONTAINS" like that:
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
My question is: for the 60 fields in the table that I want to do
full-text search for the same expression, may I write a query like
that:
Select f1, ..., fm
From table
Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
CONTAINS(f60,expr)
Or there is a more compact way to write this query.
Thank you very much for your answer,
Regard,
Djamila.that's one way. If you don't care which column the hit is found in you could
do this.
Select f1, ..., fm From table
Where CONTAINS(*, expr)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<djamilabouzid@.gmail.com> wrote in message
news:1159835993.016620.306290@.h48g2000cwc.googlegroups.com...
> Hi,
> I have a question about MS SQL Server 2005:
> I have a table in the database that contains 70 fields. I must perform
> full-text search in about 60 fields. I use for that the full-text
> searching "CONTAINS" like that:
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, '"laugh*" NEAR lager')
> My question is: for the 60 fields in the table that I want to do
> full-text search for the same expression, may I write a query like
> that:
> Select f1, ..., fm
> From table
> Where CONTAINS(f1, expr) and CONTAINS(f2, exp) ... and
> CONTAINS(f60,expr)
> Or there is a more compact way to write this query.
> Thank you very much for your answer,
> Regard,
> Djamila.
>

Contains Characters or Numbers Method

Hello,

I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL.

Thanks.

look at SQL Server function IsNumeric. It return 1 if it is, otherwise 0. You can use it with CASE WHEN. If it is numeric, get it, otherwise, set to other value such as 0.

|||

You can check whether the following will work for you:

WHEREyourColumnNOTLIKE'%[^0-9]%'

or

WHERE IsNumeric(yourColumn)=1

|||

Hey,

I didn't know there was an IsNumeric method... and I'm using SQL 2000 so regular expression's won't work for me. Sorry I should have mentioned the 2000 part.

Thanks.