Compare Two Columns With WildCard

Aug 1, 2006

I am trying to compare columns in two tables with a wildcard character.

One table: Other Table:
----------------------
ID Col1 | ID Col2
---------------------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
-------------------------------
I want to writing something like:

SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)

which obviously does not work.

basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:

1A LIKE '1*'
1B LIKE '1*'

How can I do a comparison like this?

View 4 Replies


ADVERTISEMENT

How To Compare 3 Columns From 2 Tables

Jul 26, 2013

I have requirement like this

1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns
'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column

View 5 Replies View Related

Compare Values From Two Columns?

Sep 20, 2013

I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?

View 4 Replies View Related

Compare Two Columns And Update The Third Column

May 21, 2008

 Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.

View 4 Replies View Related

SQL 2012 :: Compare Two Columns In Three Tables

Dec 16, 2013

I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Compare Two Rows And Two Different Columns

Oct 22, 2014

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',

[Code] ....

View 6 Replies View Related

MS SQL Compare Columns To Generate Display Name

Sep 17, 2007

Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas

View 5 Replies View Related

How To Compare Columns Of Two Tables In Two Different Servers

Mar 18, 2008



I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers
TABLE A: [ServerA].[ProdDB].dbo.[Orders]
TABLE B: [ServerB].[ProdDB].dbo.[Orders]


How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.

Does any one have a script that could help me with this task. Thanks

I am using SQL Server 2005

View 8 Replies View Related

Unique Constraint W Nulls, Compare Columns

Jun 20, 2008

Hi Guys.

I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?

For Example:

This is what I want

ID Column1 Column2
--- -------- ---------
1 5 8
2 5 7
3 null 10
4 6 2


This is what I DO NOT want:

ID Column1 Column2
--- -------- ---------
1 5 5
2 5 5
3 7 7
4 null null


I created a unique constraint between the 2 columns, but it still allows dups within the row. Please help me.

View 1 Replies View Related

Compare 2 Rows To Find The Difference In Columns?

Aug 14, 2014

How would I compare 2 rows to find the difference in the columns?

Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3

Result:
Column1: Text2 New
Column3: Text4 Old Text3 New

View 6 Replies View Related

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

SQL Server 2012 :: Compare Two Different Columns Of 2 Different Rows In A Data Set?

Jan 29, 2014

Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.

For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.

RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0

View 4 Replies View Related

SQL Server 2012 :: Compare Dates Between 2 Different Rows And Columns?

Feb 18, 2015

What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Compare Data (join) Based On Two Varchar Columns

Mar 15, 2014

-- My first Data

create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')

-- My Second Data

create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')

--Third Second Data

create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/

i have doubt on two things

1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second

2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found

--i tried below to solve first doubt, it is working but want to know any other wasys to do it

select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')

--Expected results wull be

create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')

[code]....

View 1 Replies View Related

Wildcard Use

Jun 30, 2006

Hello everyone and thanks for your help in advance.  I am working on an application that does a property search off of a database that contains approximately 40000 records.  The search criteria allows the use to specify a minimum and maximum price, subdivision name, number of bedrooms, etc.  I set up a stored procedure to query the databse.  if one of the parameters is not specified, i simply pass it a "%".  However, when i execute this sproc in Query Analyzer, it takes in excess of 10 seconds to return the records, even  if only one or two are returned.  I am assuming this is due to the use of the wildcard character when the user does not have a preference, but I am not sure of any other way to do this.  Any help on this topic would be grealy appreciated.

View 3 Replies View Related

Wildcard

Sep 9, 2006

hi,i have an sqldatasource a gridview and dropdownlistthe gridview is updated on the selectedIndexChanged Event of the dropdownlistmy goal is to add an item in the dropdownlist with the name ALLwhich should matches all the records in the databasei tried to put the value of the all item = * and % but neither seems to workany help on what could be going wrong would be appreciated

View 7 Replies View Related

Looking For A Wildcard

May 23, 2003

hi,

how do i contsruct a SQL query to search for the % character in a data field?

thanks

View 1 Replies View Related

CONTAINS And Wildcard

Oct 29, 2007

Is it a good idea to have multiple contains? I have this query:Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')Also, I tried this, didn't return anything:Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')Both Col1 and Col2 has the string 'Africa' and 'African' in it.--sharif

View 2 Replies View Related

Using Wildcard

Aug 22, 2006



alter procedure sp_ADsearch @tbname varchar(1000),@searchproduct nvarchar(500)

as

declare @sqlstr nvarchar(1000)

set @sqlstr='select * from '

set @sqlstr= @sqlstr + @tbname

set @sqlstr= @sqlstr + ' where name like '

set @sqlstr= @sqlstr + '%' + @searchproduct + '%'

exec sp_executesql @sqlstr

--

exec sp_ADsearch product,ee

when executing the procedure i am getting an error "Incorrect syntax near 'ee'.".

whats wrong with the syntax? how to use % along with variables

View 5 Replies View Related

Wildcard Used For SQL IN Clause

Dec 30, 2006

I am trying to control how users view records.  I want to create a solution that would, for instance permit:user A to view Store 1user B to view store 2 and store 3user C to view store 5 and store 6and User D to view all stores even if more stores were added in the future (in other words user D would have access to all records)
I want to create an 'authorization table' so that different users can see different records.  I think the easiest way to do this is to pass a parameter to the where clause, but the problem that I face is the how can I use a wildcard in a SQL 'IN' clause?  Does anyone have any suggestions.  Perhaps I am taking the wrong approach.  I would appreciate any guidance.

View 2 Replies View Related

Wildcard In The WHERE Clause

Jan 28, 2007

Hello,
I'm trying to pull some data from a table with the option to filter on 2 columns.
I've set up my sql statement to accept 2 parameters and I'd like to be able to send 1 or 2 wildcards if needed. My statement looks like this:SELECT *
FROM City WHERE CityName= @CityName AND State= @State
ORDER BY CityName
For example, if you wanted all of the cities in all of the states I would pass (*,*) as parameters.
Or if I wanted to see all of the states that have a city named Richmond, I'd pass (Richmond,*) as the parameters.
The wildcards are not returning anything and I don't know why. It works fine if I pass something like (Indianapolis,Indiana) as parameters so I think it's in my use of the wildcards that is wrong.
 Thanks.

View 4 Replies View Related

Wildcard For Integers Or Something?

Dec 10, 2001

I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.

Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.

Does anyone have any ideas here?

Thanks,
Al

View 2 Replies View Related

Wildcard Misuse?

Oct 5, 2006

I've got a text field with a list of dates in this format:

10/31/2006
11/2/2006
11/4/2006
11/5/2006

The field can contain multiple dates (as listed above). I need to query the db and retrieve dates by month and year. For example, all fields containing 10/%/06 or 11/%/%06.

My query:

SELECT * FROM tblCalData WHERE visible='1' and approved='1' and eventDates LIKE '11%06%' ORDER BY eventDates DESC

so even though a field contains dates in October and November, selecting all November dates, in this example, don't appear. The query seems to be looking only at the first date.

Is this possible? Is there another/better way to do this?

Thanks!

:grimey

View 14 Replies View Related

Is There A Way To Use Wildcard Characters...

Feb 1, 2006

to reference columns.
i have a table that has a column that is repeated 25 times, sort of.

table_1
tbl_ID (PK)
tbl_c_1 char 5
tbl_c_2 char 5
tbl_c_3 char 5
tbl_c_4 char 5
tbl_c_5 char 5
....
tbl_c_25 char 5

everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way?
I cannot change the table.

View 6 Replies View Related

WildCard If Not Value Selected

May 7, 2008

HI Guys, I have a question.
I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names).

Current Statement:
And tblRetailer_Contact.faxcontact LIKE *

I substituted:
And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’

This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected?

DECLARE @FaxContact as varchar (50)

SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5)
SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5)
SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', ''))
SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5)

SELECT tblEData.Timestamp As [TimeStamp],
LTRIM(RTrim([ResultsCustName])) AS CustName,
LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status],
FaxContact AS FaxContact,
ResultsPKey As ResultsKey
INTO tmpE_Callbacks
FROM tblEData
LEFT JOIN tblContact
ON tblEData.RetailerPrefix = tblContact.Prefix
WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date
AND FaxContact Like '%@FaxContact%'

Thanx so much,
Trudye

View 11 Replies View Related

Wildcard In Query?

Dec 6, 2007

I screwed up my database and double added data to a text field.

For example I need made this:

'http://192.168.200.1/images/' +PATH+ '.jpg'

Look like this:

'http://192.168.200.1/images/http://192.168.200.1/images/12345.jpg'

How can I query to reset this?

update perimage
set PERIMAGE_PATH = 'http://192.168.200.1/images/' +PATH+ '.jpg'
WHERE PERIMAGE_PATH <> 'http://192.168.200.1/images/' +PATH+ '.jpg'

That just makes the problem worse

View 3 Replies View Related

Wildcard In Query?

Dec 11, 2007

I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*

Im basically trying to exclude creating duplicates, where this path already exists.

Here is my code:


INSERT INTO IMAGE (FCN, IMAGE_NAME2)
SELECT FCN, Col066
FROM GRAB where Col066 <> ' '
update IMAGE
Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg'
FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*

What is the proper code to do this. I know the last line does not work. Thanks

View 7 Replies View Related

Using Wildcard With Parameters

Oct 24, 2007



The where statement on my stored proc is as follows:

Where actv.ProjID + '-' + actv.Activity = @project

@project is something the user provides in the form of "xxxx-xx-xx-xx". I would like to use a wildcard, so I changed my where statement to the following:

Where actv.ProjID + '-' + actv.Activity = @project + '%'

But this returns nothing and I am not sure why. I don't get any errors, just no results. Anyone got any ideas as to why?

Thanks in advance!

View 9 Replies View Related

Wildcard Ssl Certificates?

Apr 27, 2007

Does SQL Server support wildcard Certificates. When you install the wild cert in the certificate store, the sql configuration manager does not see it in its drop down list. Id it does, what are the steps or please point me to the right direction. Does the cert need to be specifically for that particular hostname. Thanks

View 1 Replies View Related

Query On SQL CE 3.5 With Wildcard

Jan 9, 2008

Hi,

I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException.
Here is my query with parameters :
Query: SELECT "PARAGRAPHES"."PARA_ID" AS "Numero","PARAGRAPHES"."PARA_SECT_ID" AS "Section","PARAGRAPHES"."PARA_NUMERO" AS "Rang","PARAGRAPHES"."PARA_GROUPE" AS "Groupe","PARAGRAPHES"."PARA_ENTITES" AS "Entites","PARAGRAPHES"."PARA_LBL_COURT" AS "Court","PARAGRAPHES"."PARA_LBL_LONG" AS "Long","PARAGRAPHES"."PARA_ACTIVE" AS "Active","PARAGRAPHES"."PARA_CONDITIONS" AS "Conditions","PARAGRAPHES"."PARA_PARENTS" AS "Parents","PARAGRAPHES"."PARA_QUESTIONS" AS "QuestionsConditions" FROM "PARAGRAPHES" WHERE ( ( ( ( "PARAGRAPHES"."PARA_SECT_ID" = ? AND ( ( "PARAGRAPHES"."PARA_ENTITES" LIKE ?) OR "PARAGRAPHES"."PARA_ENTITES" IS NULL))) AND "PARAGRAPHES"."PARA_ID" IN (SELECT "DOSSIER_LINKS"."DOLI_PARA_ID" AS "Paragraphe" FROM "DOSSIER_LINKS" WHERE "DOSSIER_LINKS"."DOLI_DOSS_ID" = ?) AND "PARAGRAPHES"."PARA_NUMERO" = ?))
Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52.
Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%".
Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.
and my error :
[System.FormatException] = {"@Entites2 : %D% - FormatException"}

If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.

ThanksFabien

View 1 Replies View Related

Wildcard Searches

May 13, 2006

I have a number of functions that require the input of parameters in order to ultimatly create a report under Reporting Services by making use of a Stored Procedure.

All the functions etc work as does the stored procedure, but it only works if I specify data that I know exists e.g.

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]

@dt_src_date = N'04/28/2006',

@chr_div = N'NE',

@vch_portfolio_no = 3,

@vch_prop_cat = N'core'

SELECT 'Return Value' = @return_value

GO

How can I set this so that it will wild card the value. For example rather than having to specify

@chr_div = N'NE', I could specify something like

@chr_div = N *, so it would show both NE and SW values in the result set.

Anybody point me in a direction here. I have tried % but that does not seem to work, I get a

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '%'.

Thank in Advance

View 4 Replies View Related

Wildcard In Simple Sql Search.

Mar 30, 2005

I'm very new to SQL so please forgive my ignorance...
I've made a simple .net search page which queries an sql database with the following query, (in a stored procedure):
SELECT     Category, Number, RegisteredUser, DeptName, Surname, Forename, Site, IDFROM         tblTelephonesWHERE     (@surname IS NULL OR                      Surname LIKE @surname) AND (@site IS NULL OR                      Site = @site) AND (@deptname IS NULL OR                      DeptName LIKE @deptname)
This works fine, as expected if i leave fields null or enter an exact match, but I (of course) have to add a wildcard in my search string for a wildcard search. For example, looking for 'duncan' i need to enter 'du%' or 'duncan'.
What I really want is for all searches to have wildcards behind them so only the first few characters need be inputted, and I could just search for 'd' or 'dun' without adding the '%' to get 'duncan'. I think I am aware of the implications of this approach and do want to go ahead as there are only about 850 records.
Any help or links to useful articles would really be greatly appreciated.

View 1 Replies View Related

WildCard (%) Search Issue

Dec 15, 2005

Hi all, I am currently using SQL Server 2000and am having some issues when searching a CHAR column and using the WildCard character '%'. The problem is that when I use this WildCard character, the results returned (if any!) are incorrect. For example, I know that there are many rows in the DB that have the following data in a column, 'ABERYSTWYTH CEREDIGION', but when I using the following SQL...'(WHERE (UPPER(AddrLine3) = 'ABERYSTWYTH%')'  I get no results returned.Is there a known issue with the WildCard character in SQL Server 2000?ThanksTryst

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved