SQL Pattern Matching
Mar 20, 2008
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS
--- ------- -----------
AA025 NAME1 attached, atic, common, business, hotels
AA026 NAME2 headache, medicin, aspirins, heat, health
AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID.
Thanks in advance guys.
Cheers.
View 14 Replies
ADVERTISEMENT
Mar 30, 2015
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work.
select fieldname
from tablename
where fieldname
like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
View 1 Replies
View Related
Mar 19, 2008
I've decalred this stored procedure with a simple where and like statement. The problem is I don't see any result.
Here is my code
create procedure sp_Select_ProfileNames
@NameSearch varchar(50)
as
select ProfileFirstName +' '+ ProfileLastName as ProfileName
from Profiles
where ProfileLastName like '%@NameSearch%';
When i change this line:
where ProfileLastName like '%@NameSearch%';
to
where ProfileLastName like '%Bil%';
I see names starting with Bil, but when i enter Bil as param, I don't get anything.
What's wrong here ?
View 2 Replies
View Related
Aug 25, 2006
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
View 10 Replies
View Related
Oct 29, 2007
I have a table called MessageBoard. It has a column called Messages.
A user can type text including any html tags through a text area ans when he saves it by clicking a button, the content typed by the user is saved in the MessageBoard Table (in the Messages) column. So once saved, the html tags are kept intact. If I have to find and replace certain html tags, what kind of SQL Query I have to write?
For example I want to find all the <pre> </pre> tags and replace it with <p> </p> tags. How do I do this?
View 6 Replies
View Related
Apr 22, 2005
The LIKE keyword in SELECT WHERE can use wildcard
%
Any string of zero or more characters.
_
Any single character.
but how to Pattern Matching for "zero or single character"?
View 10 Replies
View Related
Aug 30, 2007
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.
View 1 Replies
View Related
Dec 20, 2013
I have a set of data where a column contains titles which have been formatted as follows:
"FirstWordSecondWordThirdWord...." etc.
That is, all the words have been concatenated but can be visually separated by their capital first letters.
For reporting purposes, I need to break this column into the separate words so that it looks like:
"First Word Second Word Third Word...." etc.
how this can be achieved?
View 9 Replies
View Related
Feb 20, 2014
I have a table ("MyData") with string columns that have nvarchar data that looks like this:
ColA
--------
42/90
78/109
I plan to do a mathematical grouping on the numerator (eg: 0-10,11-20,21-30, etc... ), So I need to grab the numerator and turn it into an int. For reasons I can't get into, I have to do this in pure T-SQL.
So my question: How would I write a select statement that regex pattern matches "^([0-9]+)/" on ColA and returns integers instead of text?
E.g. THis Query:
SELECT [magic t-sql syntax] as Converted_ColA from MyData
should return this set of int values:
42
78
View 8 Replies
View Related
Sep 17, 2015
I have two tables in SQL Server--an expression table and a translate table.
DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)
INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'
[Code] ....
I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.
So,
CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67
View 8 Replies
View Related
Feb 8, 2008
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
View 9 Replies
View Related
Jan 6, 2015
I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:
UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance
I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same
Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082
The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.
Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7
good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.
View 9 Replies
View Related
May 8, 2015
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?
View 2 Replies
View Related
Aug 18, 2006
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
View 1 Replies
View Related
Feb 4, 2007
Hi All
I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.
I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data
In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible
As always any help or direction would be appreciated.
View 5 Replies
View Related
Jan 30, 2008
hi can any one help me to get the pattern of 70-441 exam.
i need information total number of question in the exam..
and will there be any simulation as 70-431 exam.
View 3 Replies
View Related
Sep 15, 2005
Say I have an object that can be created by some users and not others. And, I have a method something like - function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do Ia) have a separate output parameter to report on the success of the creation (e.g. @Err Int OUTPUT)b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)c) somehow try to cause a failure of the SP that can be caught in a try/catch blockThanks in advance,Martin
View 1 Replies
View Related
Aug 22, 2007
We have a collection of charts, some of which we use the gray scale for the palette (users only have B&W printers). We need to use the same patterns in other charts for consistency. The only option I see in Edit Chart Value-->Appearance-->Series Style-->Fill is Gradient. Is there any way to pick a fill pattern?
The pattern is the diagonal lines slanting down from left to right. It is the third column in the gray scale, so I have tried to add a dummy column as the first column, so that my column would have this pattern. However, the chart allows for space width wise for the first column, so there is a blank space on the chart. I have the Data Output checked to No.
Any other ideas, I would appreciate it!
View 2 Replies
View Related
Mar 12, 2008
Hi,
How can i find the 2nd comma position of a string, if i have multiple commas in my field. I need SQL Statement
Ex:
F1
Andy,David,Martin,
Sam,Dan,Philip,
I need the position should be
F1 F2
Andy,David,Martin, 11
Sam,Dan,Philip, 8
Thanks,
Mears
View 8 Replies
View Related
Jun 7, 2007
I have two simple tables:
CREATE TABLE summaries ( summaryId int identity, summary varchar)CREATE TABLE phrasesToSearchFor ( phrase varchar)
I want to do this:
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))
View 1 Replies
View Related
May 15, 2007
Hi,
We are using FULLTEXT searching for searching the title column in SQL 2005 table.
We are aware that using CONTAINS we cannot search middle of the string like the way we do using LIKE '%text%'.
Is there any alternative way of acheiving this functionality using FULLTEXT search or any other ways of doing this keeping in mind performance.
Any feedback on this is greatly appreciated.
thanks and regards,
Krishna.
View 3 Replies
View Related
Apr 30, 2015
I need a simple tSQL code that will produce an array sequence values that has gap with 2 or more consecutive values, which can vary - so 2 or 3 or 4 between sequence of number. For example, I want it to return a series with a gap or space of 2- So it should return 1, 2, 5, 6, 9, 10.. or a gap of 3 - 1,2 ,6,7,11,12.. etc I know a single gap is quite simple using a recursive cte, but I haven't been able to find how to get this kind of result.
View 14 Replies
View Related
Dec 11, 2007
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.
View 4 Replies
View Related
Aug 20, 2014
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
View 9 Replies
View Related
Aug 31, 2005
I am trying to do a select statement with SQL 2000 for all recordscontaining any number of lowercase letters. I have tried the following2 statements and they both seem to match both lowercase and uppercaseletters.SELECT * FROM customers WHERE name LIKE '%[a-z]%'SELECT * FROM customers WHERE name LIKE'%[abcdefghijklmnopqrstuvwxyz]%'Can anybody help?
View 4 Replies
View Related
Jan 3, 2007
Hi,
I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.
However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.
Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.
Options explored so far:
1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.
Your help in this matter will be appreciated.
Regards, Tonn
View 5 Replies
View Related
Aug 9, 2007
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p>
<p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows:
http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG
http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
Any idea?
View 4 Replies
View Related
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'
[Code] ....
View 6 Replies
View Related
Jul 16, 2007
i want to apply a hatch pattern to the fill colour on my bar graph and it doesn't work. i select fill colour, then i select hatch colour, i preview and it doesn't show the hatch pattern.
i would use gradient only i use 2 colours the same (hence why i want to differ them in a way other then colour) and they show up the same in the legend - legends don't display gradients
View 1 Replies
View Related
Dec 5, 2007
Hello, we've developed an application that runs on AXIM X51, if it is an important to know.
The data layer handles the connection using the singleton pattern, a bit of code of this is:
(....)
Private Shared ceConnection As SqlCeConnection
(....)
Public Shared Function GetConnection() As SqlCeConnection
Try
If (ceConnection Is Nothing) Then
ceConnection = New SqlCeConnection(Me.connectionString)
End If
If (ceConnection.State = ConnectionState.Closed) Then
ceConnection.Open()
End If
Return ceConnection
Catch ex As Exception
Throw ex
End Try
End Function
(....)
So far nothing strange. We started experiencing problems when the end-users get an "Unspecified error" which later debugging it we got the href value inside the exception object is -2147467259 and nothing else. And if that wasn't enough I couldn't replicate the error in my pocket.
The only way I got the same error was removing the SD Card where the db is located at the same time the application was still open.
Does anybody experienced the same problem??? I researched about this kind of errors "when the device is just restored from the suspend mode" but with out luck.
Please, I really appreciate any help with this issue. Any comment will be more than welcome.
View 3 Replies
View Related
May 20, 1999
I like to schedule a task that provides with database usage size, current users online, memusage and CPU usage and store it into a database table once in a week. I tried looking at different system tables but not successful.
We are using MS SQL 6.5.
Can someone help me on how to achieve this?
Thanks,
Mohan
View 1 Replies
View Related
Oct 27, 2015
I am learning the string functions and the database that I'm using is AdventureWorks2012.However ,while practicing I was just trying to hide the phone number with '*' mark,but not getting the desired result.So the code is.............
SELECT
PhoneNumber, (SUBSTRING(PhoneNumber,1,2)+REPLICATE('*',8)+
SUBSTRING(PhoneNumber,CHARINDEX('[0-9][0-9]{1,2}',PhoneNumber),LEN('-555-01')))AS[PhoneNumber]
FROM
Person.PersonPhone
[code]....
View 6 Replies
View Related
