********************
alt.php.sql,comp
databases.ms-sqlserver
microsoft.public.sqlserver.programming
***********************************
Why doesn't this work:
SELECT *
FROM 'Events'
WHERE dayofweek
REGEXP 'monday' OR description REGEXP 'monday'
When this does work:
SELECT *
FROM `Events`
WHERE dayofweek
REGEXP 'monday'
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
Hi
I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?
Is it possible to put a set of columns into a select statement using "in"
For example:
select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)
This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.
Any ideas?
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.
What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.
View 9 Replies View RelatedI've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
Hello everyone,sorry for my bad English. I have to create a query under mySQL for asmall research engine; I have to rate the results of a simple regularexpression matches inside a text field. I gave a look to the function REGEXPthat mySQL offers, but it seems do give just a boolean result, 1 or 0!!!What i need is that the regexp funtion gave back the number of the totalmatches occured inside a field of every line. Do you know if mySQL offersthis opportunity? Maybe using onother function instead REGEXP?Thank you all,Federico.
View 1 Replies View RelatedHi guys,Sounds a bit strange, however, if we could put some calculation instored procedure it would be quite convenient, just... where can I finda REGEXP library for matching checking? thanks.yours,athos
View 11 Replies View RelatedHi,
I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query:
select *
from table1 tb1, table2 tb2
where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
Thanks,
--Yonah
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Please can anyone help me for the following?
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
for eg:
data
Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760
i want output for the above as:
Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
I concatenate multiple rows from one table in multiple columns like this:
--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
Hello All,
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
Any help is much appreciated!
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
Hi everyone
I am trying to create a stored procedure that will searches any of the following different columns of the defferent tables
[dbo].[Store]
[dbo].[StoreType]
[dbo].[City]
[dbo].[Province]
--I joined the 4 tables below
SELECT dbo.Store.Store, dbo.Province.Province, dbo.City.City, dbo.StoreType.StoreType
FROM Store INNER JOIN
dbo.Province ON dbo.Store.ProvinceID = dbo.Province.ProvinceId INNER JOIN
dbo.City ON dbo.Store.CityID = dbo.City.CityId INNER JOIN
dbo.StoreType ON dbo.Store.StoreTypeID = dbo.StoreType.StoreTypeId Where 1 = 1
--I have 2 parameters
1.@varSearch varchar(50)
2.@varProvince varchar(50)
in @varSearch parameter You can search any thing from [dbo].[Store].Store or [dbo].[StoreType].StoreType or [dbo].[City].City
and @varProvince will be [dbo].[Province].Province
the output must show every Store,StoreType or City in the province
Hi,
How to search for a value across all columns in table in Tsql or in SSIS
Cheers
I have the following results:
ID, Office1
1, Testing
1, Hello World
What i am trying to do is to get this result:
ID, Office1, Office2
1, Testing, Hello World
how i can accomplish this task.
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I need results for each parent like this
ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
View 1 Replies View RelatedI drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?
View 2 Replies View RelatedHow to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
This is a true newbie question. I want to search an entire table for a string. Is there a way to do the equivalent of:
View 5 Replies View RelatedHi ,
If asked to find all the columns across tables which contains any NULL or blank value then what could be the best approach ?
I am sure there must be a way other than checking each column of each table .
Can anyone help me out in this .
Cheers
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
View 2 Replies View RelatedHow can I run a query that searches for a reference number that may appear in two different columns? I want to pull all records for idenification numbers that can also show up in a secondary identification column.
View 4 Replies View Related