I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis
Hello - I was wondering if anyone knew how to do this -
I have a database with a field for Id, LName, GName, DOB. In the LName field, some of the names have * placed after the names. Is there a way I can search for the entries in LName with the * in the record?
Thank you!
Liz
Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,
CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;
CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.
CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.
Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.
I have written a code but I can't get it look into specific CLSID and SCLSID.
For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'
I couldn't attach either the file or image.
How can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
Is it possible to format just part of a field? I've got a report that return a collection of users based on some search criteria. Bascially, you enter a string and the report returns all users with a user name or display name simialr to your search string. I wanted to highlight the part of the name that matches the search string.
For instance, if you search for 'nny'
You might see a result of 'Johnny User'
Thanks.
J
How would I accomplish update just a part of a field? I have 500+ records in a table that got written as \share 01234.jpg, \share 01235.jpg...\share 01734.jpg but the records should have ended with .tif instead. Now the application cannot find the correct image because the extension is incorrect. How can I use the Update statement to just change the extension on these records?
Thanks.
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies View RelatedI have three input fields that I want to pass the values into a stored procedure.
It is not requried to enter in all three text boxes. What would be the best way to go about ignoring the other two text boxes if only one text box has input when trying to build the select statement
Hi,
I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue
My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers.
Thanks
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?
View 9 Replies View RelatedHi,Is there any way to find all stored procedures that contain a givenfieldExample: I want to find all stored procedures that work with the fieldShipDate in tblOrder tableThanks, Eugene
View 3 Replies View RelatedHi all.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate
FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID
WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
Thaks in advance for any assistance
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)
SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'
SELECT @patfind = '%' + @find + '%'
UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind
MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail:
SELECT * FROM Expression WHERE ExpText LIKE '%≤%' -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText) -- no records
However, SELECT PATINDEX('%≤%', 'y ≤ 200') will return 3.
Any suggestions? Thanks in advance.
i am trying to search an SQL database to retrieve all names from employee table who have a birthday today. this needs to automatically fill in the date parameter with the system date. this is what i have so far:
sub page_load(sender as object, e as eventargs)
dtmDate=DateTime.Now.ToString("M")
con = New SqlConnection("Server=Localhost;UID=******;PWD=*****;Database=Pubs")
cmd = New SqlCommand("Select fname, lname From Employee where dob='& dtmDate'", con)
con.open()
dtrBday = cmd.ExecuteReader()
rptBday.DataSource=dtrBday
rptBday.DataBind()
dtrBday.Close()
I know this isnt quite right. i get errors when it hits my repeater.
the error i am getting is :Syntax error converting string to smalldatetime data type.
if someone could give me a push in the right direction here it would be greatly appreciated.
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records.
I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time.
I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410.
The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start.
This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
Hi Guys,
I have started a project using Linq for sql and SSCE. Everything goes well until I realize that SSCE can only run in full trust. Linq for sql is planned to support partial trust scenario. Any plans for such a support in Sql Compact Edition?
Dany
The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date.
What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows...
I appreciate any help,
@PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END
In our database we have the concept of 'Companies' each company has an entry in a tblCompanyControls using a field lCompanyNumber to uniquely identify it.
Company specific data is then grouped within additional tables using this
lCompanyNumber.
Thus all the departments for a particular company (eg 4) exist in a table
tblDepartments with lCompanyNumber =4. The same applies for pensions, pay
elements, employees and so on.
Each employee has various attributes stored in several tables, thus the
main data is stored in tblEmployees with an lCompanyNumber to illustrate
the company they belong to. And a system generated lUniqueID that is the 1
to many relationship to tables such as tblEmployeePayElements,
tblEmployeePensionSchemes.
These in turn have a primary key that establishes a 1 to many with the
period data for pay elements etc.
What we want to do is replicate only the data for a specific company, for
tblEmployees, tblDepartments this looks straightforward as i just set a
filter like 'tblEmployees.lCompanyNumber = 4'. My problem is how do I
replicate just the tblEmployeePayElements for those employees that are in
the specific company as the table does not contain the lCompanyNumber. Can
you replicate a view? I an quite ignorant of replication and would really
appreciate some pointers. Note DRI is not in use.
I am are running SQLSERVER SP4 on WINNT SP3.
I am serious problem of partial update my query is
something like
bEGIN tRAN
declare cursor...
SElect * from tableA where flag = null
open cursor
fetch first...
while @@FETCH_STATUS = 0
BEGIN
UPDATE TABLE tableB ..
.
.
.
fetch next
END
CLOSE ...
DEALLOCATE..
COMMIT TRAN
The problem is the cursor select retrieves
says about 10000 rows, goes thru the
loop for 1000 rows and just terminates without
giving an error message,or rolling back
in case of errors, but comes out as successfully
completed.
I am at loss as what could be the problems..
any suggestions welcome..
Thanks in advance,
Balajee.
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select *
into #temp_2
from #temp_1 a, Server2.DBa.dbo.table2 r
where r.field1r = a.field1a and
r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific:
r.field3r looks like 000884
a.field3a looks like 17445000884
So- I just want to pull in the 000884 off of a.field3a
Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".
i.e.:
rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com
etc like that.
each row varies with one exception. administrator@sthou.com is in each one.
is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?
thanks again for any easy answer
rik
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC
I have been asked to see what I can recover from a development server whose database became suspect during a power failure.
One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.
There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.
What are our options here?
Are their any good third-party tools to help us with this problem?
Hi all,I have 2 files containing Id numbers and surnames (these filesessentially contain the same data) I want to select distinct() andjoin on id number to return a recordset containing every individuallisted in both the files HOWEVER, in some cases an incomplete IDnumber has been collected into one of the 2 files -is there a way tojoin on partial matches not just identical records in the same way asyou can select where LIKE '%blah, blah%'??Is hash joining an option i should investigate?TIAMark
View 4 Replies View RelatedHi All,I have the following scenario. I have a table called Invoice. Thishas around 30 columns of which i have to do a retrieval based onfilter conditions on 10 columns. These filters need to be partialsearches i.e. for e.g the Customer name could be 'Arun', 'Parthiv','Aaron', now i should be able to search the customer as 'ar' and itshould return 'Arun' and 'Parthiv'. My concern is there are 10 columnson which this like '%x%' search has to be done and there willpractically be hudreds of thousands of rows. can anybody suggest me toimprove the performance of such a query. Currently what i am thinkinof isselect Id, Memo, .. FROM Invoice where CustomerName like '%' + @Name +'%' and etc.P.S. am using ASP.Net as the front end.
View 1 Replies View RelatedHello All,
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
Thanks in advance for any help
-Clint
Hi,
I have a file where there is a partial row at the end. It doesn't cause an error, but I get a "partial row" warning during execution.
What do most people do with these partial rows? Do they just ignore them as long as they don't cause errors? Or is it better to handle the partial row with a conditional split, for example?
Just wondering what other people's thoughts on this are. I tend to be of the "get rid of it" camp, but maybe that's overkill? Just looking for opinions, best practices.
Thanks
hi
I have 10 pages in my report and i want to export only 3 page .
any body can help me on this
Thanks
Pratik Mehta
Hi - I am using partial backup & restore on a Data Warehouse database currently in development.
When I recently tested the restore procedure I got the following error when trying to an online restore of one of the ReadOnly filegroups:
Msg 3125, Level 16, State 1, Line 1
The database is using the simple recovery model. The data in the backup it is not consistent with the current state of the database. Restoring more data is required before recovery is possible. Either restore a full file backup taken since the data was marked read-only, or restore the most recent base backup for the target data followed by a differential file backup.
I believe I received this message as the Filegroup I was attempting to restore had been set ReadWrite since it was backed up.
So - I am looking for a query to test that all my filegroup backups are consistent with the live database.
I think I can achieve this by checking the read_write_lsn & read_only_lsn values for the filegroup to restore are the same as the values in sys.master_files for the live database.
I am reading the lsn values for the backup from msdb.dbo.backupfile
Can anyone confirm this is the correct approach? or is there a better way to do this??
Many Thanks
I have a question, I keep getting the warning where the lookup found duplicate reference key values when caching reference data. It also says to switch over to partial or no cache mode. I switched to partial and and it took way longer to run my package. I have around 200,000 records right now but this table will continue to grow. I guess my question is which mode should I be using? I have another table that will have millions of records in it.
thanks,