T-SQL (SS2K8) :: Isolating A List With Pattern Search
Jul 14, 2014
I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.
WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'
SELECT summaryFROM summariesWHERE CONTAINS(summary, <any of the values in phrasesToSearchFor>)
I've tried every weird permutation of the query I can think of, but can't find a way to make it work. Can anybody help?
Bonus Question:
I've got a function dbo.getPhraseToSearchFor(userId) that returns a phrase depending on the value of userId. I want to get a result table of (userId, summary) where for every user, I use dbo.getPhraseToSearchFor(userId) to figure out what to search the full-text catalog for. The docs say you can use a scalar function anywhere a scalar value is expected, but apparently that doesn't apply in this case -- you can't do CONTAINS(summary, dbo.getPhraseToSearchFor(23))
hi i have a table with a column which contains a large paragraph of data. i need to search for a pattern within this text and update it.
for example the column contains 4096 char. within this text, i search for a pattern"qwerty" and convert it to "asdfgh". any ideas how this can be done? i'm using sql server 2000 i have to write something like UPDATE [DB].[dbo].[Table] SET [Column1] = '.............', WHERE .......... any help will be appreciated.
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result. 2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS ( SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL SELECT 'more_stuff.yoursminehershis_falafel', 'yours first' ) SELECT string, pattern, ca.item, ca.itemnumber FROM testctes CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
I'm not quite sure that this is possible but, I figured that I would check with you experts out there before trying a new approach. I've done quite a bit of research and have not seen anyone quite figure this out yet.
We have a SQL Server 2005 application that stores and indexes documents to the database as an image data type. I'm able to do full-text queries against the documents without any trouble. I begin to run into problems when trying to pattern match social security numbers and drivers licenses stored in a full-text index. I have a user defined function that I call which runs my regular expression that checks for hits of a ssn or license number in the index. I have no problem getting hits when the data sits in a column.
I do need to mention that I have no trouble when searching for a ssn with a fixed value and where I know the ssn (ex: 123-45-6789). I am actually trying to find the existence of the pattern of ###-##-#### (ex: ^d{3}-d{2}-d{4}$) anywhere in the index. Any help would be very much appreciated.
I tried creating one simple bar chart report in SSRS, with background fill with pattern "UpwardDiagonal", the stripes on the column when viewed insider report viewer control is quite prominently visible, whereas when exporting the same report to PDF the lines are very thin and dense and hence makes it very difficult to differentiate between one column with pattern inside and one without. I have even tried various other pattern like "LightUpwardDiagonal" and "WideUpwardDiagonal" but all have same result when exported to PDF.why?
I have a rather intensive load on a server that has a data mapping application on the same server(I know this is a no no but I didn't have a say). It seems to be maxing the CPU. Is there a way on a dual or quad processor machine to tell SQL Server to only use one of the processors?
hello Since the past couple of months we have been seeing the tempdb grow from 5GB to 22GB about once every week. The production database is about 35GB. Since there is quite a bit of activity on the server i am finding it difficult to try to isolate what caused this sudden increase in the tempdev size. The actual data in tempdev less then 2 MB but it holds on to the 22GB till i go in and shrink it. This has led to us having serious space issues on the server. Any advise on how i would be able to trap the query that might cause this behavior would be great.
I have a SQL CLR Trigger, which has bizarrely (to me anyhow) requested full UI rights from CAS, SQL understandably isn€™t permitting this.
Code Snippet
The protected resources (only available with full trust) were: All The demanded resources were: UI
I understand why SQL is failing, but what I€™d like to find out is how can I debug / isolate the offending code so I can refractor accordingly. The namespaces I€™m utilising are as listed below; quite how or why any of these need UI requests is beyond me.
System System.Data System.Data.SqlClient Microsoft.SqlServer.Server System.Transactions System.Text System.Security.Permissions System.Text
Does anyone have any experience or tips on what could be tripping me up here or how to isolate the offending call which SQL is failing?
Many thanks in advance for any help you may be able to afford me.
I am trying to isolate a specific record (and a value within that record) where I have selected records based on a max(date). Some records come back duplicated because there are duplicate max dates.
For example:
SELECT
O.ID, O.Date, O.Amount = Case when o.Value in ('6','7','H','I') then 1 else 2 END , O.Value
FROM Table O WHERE o.Date = (
SELECT
MAX(O2.Date) FROM Table O2 WHERE O2.ID = O.ID )
The problem is that I get some ID's with Multiple records because they have 2 different O.Values. My table looks like this:
10/12/07 A $5.00 10/12/07 B $7.00 10/10/07 C $8.00
I want to find the Max(O.Date). Since there are two, then I want to find any records with that date that have an O.Value = B, if there isn't any, then I want any with an O.Value = A, if there isn't any of those, then any records with the O.Value = C. I basically need the O.Amount depending on the hierarchy of the O.Value.
I hope that makes sense. Any help would be appreciated.
I need to search all tables in my user database to find a value. I don't know what the column name is.
I know of one table that contains the value, but I need to look through all the tables and all there columns to see if I can find if this value exists in more than one place. It is an int value - 394789.
Can anybody provide an example of how to specify a column list in a CONTAINS statement? Documentation says it must be comma separated inside parenthesis - I have tried (every combination of) this but always get the error "Incorrect syntax near '('.", for example:
SELECT .... WHERE CONTAINS((Name,Description), '"options"')
This works fine when just querying one column (without the parenthesis).
Select * from TableName Where Contains(ColumnToBeSearched, 'testtosearch')
QUESTION: Can the 'testtosearch' be another table field like ''' + <AnotherTBLField> + '''Is there anyway to make the population of 'testtosearch' to be based on another table field (say when you join to it)?If not what else can I do, another function maybe?
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
I am trying to figure out an automated way to get a list of all the tables used in a set of 275 T-SQL scripts. i do not want to go through each script manually, look at all the from clauses and write down the tables used.
I have managed to get all the 275 scripts into a table on SQL Server 2008 R2 database. one column per each script, so there are 275 rows in the table.
FYI: each query can have multiple result sets, so multiple from clauses can exist.
For Example:
Query 1 = SELECT * FROM Table1 a INNER JOIN Table2 b ON b.Field = a.Field LEFT OUTER JOIN Table3 c ON c.Filed = b.Field
UNION ALL
SELECT * FROM Table4 a INNER JOIN Table5 b ON b.Field = a.Field LEFT OUTER JOIN Table6 c ON c.Filed = b.Field
Are the XML tags searched, as well as, the contents within the tags, when an xml column is included in a full-text index? Or is it only the contents within the tags? Would I need to combine full-text search with XPath/XQuery to get at the actual tags?
I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules.
Basically I have a massive custom search query which I now have filter even further. [URL] for previous discussions on this query, which I'm currently happy with).
so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.
each transaction can be see by
* Everyone If no record found in security table for transaction.
* If even just one transaction found, the below have permission to view it.
- Person who raised transaction
- person currently responsible for it.
- person currently working on it.
- everyone in the security table for this transaction.
- Admins for this Transaction Type.
So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.And I think I have them all EXCEPT someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.
The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.
ALTER PROCEDURE [dbo].[uspJobSearch] @csType as nvarchar(20) = '-1', @status as nvarchar(20) = '-1', @startID as nvarchar(50) = '', @endID as nvarchar(50) = '', @complaintType as nvarchar(50) ='',
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , OperationCodeVARCHAR(10) NOT NULL , EmployeeCode VARCHAR(10) NOT NULL
I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data key1 key2 647942600014 647942285437 2324662490640 2324662285437 2324662066128 2222 2285437 2222 1111111 9999 1111111 9999 2222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.
The data is:
Branch Folder ID Pax TktNo BookingRef HQ 123 1 Jim 4444 ABCDE HQ 123 2 Bob 5555 ABCDE HQ 123 3 Jim 6666 ABCDE HQ 123 4 Bob 7777 ABCDE HQ 124 1 Jenny 8888 FGHIJ HQ 124 2 Jenny 9999 FGHIJ HQ 124 3 Jenny 3333 FGHIJ
I somehow need to get a function to pull the data out for each booking ref like so
--BookingRef ABCDE Jim 4444/ 6666 Bob 5555 7777 --BookingRef FGHIJ Jenny 8888/ 9999/ 3333
I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup from updatedb left join opencall on updatedb.callref=opencall.callref
where udindex = '0' and suppgroup = 'SUPPORT' and (updatetxt like '%' + @Word + '%')
And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.
They would like it to be more fuzzy with "and" and "or". How can I adapt this?
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh', @SearchString VARCHAR(10)= '_', @OccuranceNumber SMALLINT = 1 DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0 WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
I am trying to find a way to add into a table a flattened (comma seperated list) of email addresses based on the multiple columns of nformation in another table (joined by customer_full_name and postcode.
This is to highlight duplicate email addresses for people under the same customer_full_name and Postcode.
I have done this using a loop which loops through concatenating the email addresses but it takes 1minute to do 1000. The table is 19,000 so this isn't really acceptable. I have tried temp tables, table variables and none of this seems to make any difference. I think that it is becuase i am joining on text columns?
