where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
i am working for a Library Project. in that project i want to search Book by putting book content. in book content there could be single quatation e.g William's. when ever i put single quatation my string type is terminated and my query is not executed successfully. select Book_ID,Title,Auther from book_details where Contents like '%xyz%' here xyz is my object name. if i put William's in xyz it does not work. plz tell me how to solve this problem. Jasim... New Delhi, INDIA
dear friend, the problem i am finding is moreover a concatenation problem what i did is that have stored procedure which is accepting any character and i have to show all the records where this character is existing it is like this only ******************************** name1 like '% @str %' but then it is not treating the str as a variable and treating it as a string so if you have any solution please let me know early please give the answer with example waiting for reply ashish bhatnagar
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
I have a store procedure that search by Firstname and Lastname. I want it search by either both (Firstname and Lastname) or any of them. For example if only FirstName passes to it shows all the record with that Fistname. Currently I have to pass both Firstname and Lastname to my store proc to get the result.
This is my stor proc:
USE [CustomerPortal] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name]
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
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?
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric or non-printable characters. For instance, if I have a table with a 20 character name column with names with beginning ranges from A-Z, why doesn't the following return any rows: select * from table where name < CHAR(126). In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does not include characters outside the alphanumeric range. Any ideas ?
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
That would be just the rows 1 6 3 0 2 6 1 0 2 5 3 0
If I use this:
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped from (SELECT [CustomerID] ,[PartNum] ,[QtyInOrder] ,shipped , row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1
there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped from (SELECT [CustomerID] ,[PartNum] ,[QtyInOrder] ,shipped , row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0
I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc. The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'. I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that: <FilterParameters> <asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" /> </FilterParameters> But I don't know what to do for the FilterExpressions. currently I just have: FilterExpression="DestinationName LIKE '{0}%'" i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.
So is there any other way to handle multiple criteria in T-SQL ?
Im sorry if this has been covered, I tried a search but I couldnt seem to find what I was after.
Anyway, I need an Update procedure which is actually several bunched into one. Ive had a stab at it myself, and perhaps my pseudo SQL might explain what I need..
Code Block UPDATE [TW].[dbo].[TBLSalesPart] SET
CASE WHEN [Part] = 'MONTV-' AND [YN] = 'False' THEN [SubCategory] = 20440 END CASE WHEN [Part] = 'TC-' AND [YN] = 'False' THEN [SubCategory] = 20444 END
Hi. I have this method in a class, it produces a string value based on the value of another property in the object (which represents a field in the database). I would like to turn this into a computed column in SQL server... but need help converting the formula if this is even possible. Thanks in advance.public string GetVendorEvalRating(int vendorevaltotal) { string vendorevalrating = "";if (vendorevaltotal >= 26) {vendorevalrating = "Critical"; }else if ((vendorevaltotal >= 10) && (vendorevaltotal <= 25)) {vendorevalrating = "Material"; }else if ((vendorevaltotal >= 0) && (vendorevaltotal <= 9)) {vendorevalrating = "Minor"; }return vendorevalrating; }
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/ SELECT a.[naics] ,a.[ust_code] ,a.[port] ,a.[all_qty_1_yr] ,a.[all_qty_2_yr]
[Code] ....
output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem diwhich contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.
This is my basic code, but I keep ending up with duplicate work order lines in my result set.
Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg, wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost] From worksorderhdr wh Left Join inventory iv On iv.item = wh.itemcode inner Join models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1 order by wh.date_created
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr SET SMiles = IF Dist<250 THEN Round(Dist-25)*1.15) ELSE Round(Dist-40)*1.15) END IF
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag 1111296.54 3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id 100000001 200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag 1111296.54 3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id 100000001 (Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs) IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours], wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
update wce_contact set blank = 'missing' where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')
I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?
I guess it would be a case of adding another bracket somewhere but im unsure?
I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:
SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......
WHERE CostFlag = @InputParameter
GROUP BY Dept, Project
The code above works great - but only for a single column. I need to return a dataset like this: Dept Project Cost_Mean Cost_Range D1 D1P1 495 135 D1 D1P2 960 70 D1 D1P3 1375 105
I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.
I always submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup
So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.
I cant figure out how to create the SQl script for this SELECT?
