Hi,
I'm not a big friend of MSSQL, but I have to do one query I've done for
mySQL.
But I don't know how...
I have to select 'user' from 'db' where first letter is E or N, second is B
or 0 and after that there are 6 or 7 digits I know.
How can I do that?
In mySQL it would be something like:
SELECT * FROM `table` WHERE `account` regexp '^[EN][B0]123456$' ORDER BY
`Id`;
Thanks in advance,
Martin
Hi Guys
I have a table in which one of the column name is "NAME".
I have to write query in which column is not null and if there is something then check for 'trust' or 'trustee' in the string. and if found don't pull that record. skip it
who do i do these. Any help please.
HiI'm looking to return rows from a table where a column is LIKE a keyword. This is my current code:SELECT DISTINCT business_name FROM Table WHERE business_name LIKE @keyword + '%' ORDER BY business_name ASCThe problem with this is that if a business is called "The White Ship Inn", and the keyword to match is "White Ship" it will not match. If i put another wild card in front of @keyword then this finds too many matches - i need it to match against columns which either start with the keyword, or have a whitespace in front of them. Is there some kind of T-Sql i can use or will i have to go down the regex route (using this (http://www.codeproject.com/KB/mcpp/xpregex.aspx)) ?cheers
View 2 Replies View RelatedDoes SqlServer support Regular expression?
I want to know if it's avilable or not...
And if it's not available, I want to know any other efficient way to validate string.. manipulating Regular expression style validation.
Thank you all...
I need to remove all the [ and ] in every sql stmt where they are usedto close encircling numeric value, fyi, all these sql stmt areconverted from an Access db. For instance,select *from XYZtblwhere fieldA = [1] or fieldA = [2] or fieldA = [3]to beselect *from XYZtblwhere fieldA = 1 or fieldA = 2 or fieldA = 3-- and of course I'd have written in as follows, but that's not-- the pointselect *from XYZtblwhere fieldA IN (1,2,3)Thanks.
View 5 Replies View RelatedI am try to build a query which only matches whole words and so far I've got this.
Code:
SELECT *
FROM tblSearchWords
WHERE CorrectSpelling LIKE '%[^a-zA-Z0-9]blah[^a-zA-Z0-9]%'
This will return rows which contain the string 'blah' without any numeric or alphanumeric characters beside it. However it doesn't return the rows where 'blah' is either at the start or end of the string as it expects any character except a-zA-Z0-0.
Is there any way to accept string when there is nothing on either side as well?
Thanks,
Goran
I am trying to exclude all strings that has 'a' inside (I havesimplified the actual problem)select 1where 'bb b a dfg' like '%[^a]%'However, the above does not work. By the way, I can not use 'not like'such as:select 1where 'bb b a dfg' not like '%a%'Although the above will work but the idea is that I have to use 'like'and not 'not like'. This is partly because I have to exclude rows froman exclusion table (a table that has many rows that will be excluded).Actually I want to include all srings that has lets say // in it usinga regular expression. I would like to write it as (I am sure it willnot work):select 1where column like '%[^/][^/]%'That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etcand include strings like: 'aaa aa aa' or 'bb aa nn' etcIs there any way to write a regular expression to do it? Otherrwise Ihave to solve this problem without using regular expressions in theexclusion table.Thanks.
View 1 Replies View RelatedHi
I have a table which stores a big chunk of html text, which I have to search for and replace some strings.
How can I write a query that makes use of regular expressions to do the following:
Below is a block of example text
======================================================================
" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10> <img height=10 alt="" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10><img height=11 alt=""
http://aaaa/bbbb/cccc.nsf/blablablabla?OpenDocument src="http://aaaa/bbbb/cccc.ns http://aaaa/bbbb/cccc.nsf/d8d4f0dfa09ba71142256cc50040ee51/ee9b4493dac727be422571c30027df61? OpenDocument" target=_self>
======================================================================
Now I want to search and replace the string that is highlighted in red with something like "FOUND"
The below string is what I want to search for - I will used PATINDEX on the string below to find the start index of the string in the body, and then I will add on 114 to the when doing a replace:
'%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%'
For example
REPLACE
(
TheField,
SUBSTRING
(
TheField,
PATINDEX(''%http://aaaa/bbbb/cccc.nsf/%some guid%/%some guid%?OpenDocument%', TheField),
,114
)
,
'FOUND'
)
QUESTION:
what would the regular expression be and how does one type it in the query?
I tried something like the below but it does not work, please advise .....
[a-z][0-9]{32} %/% [a-z][0-9]{32}
I have as csv-file wich I import into an SQL Server table. Now I want to do some checks on it. I use a conditional split to direct data to the other tables (1 table for the correct data, 1 table for the rejected data).
Is it possible to use a regular expression in a case in a conditional split to check if a columns has the right format?
If yes? How do I do that?
If no? What is the alternative?
Thanks!
Hello everyone
I'm new with Reporting services, so my problem is that i want to show what filter the user enter
exemple :
Data between 12/1/2007 and 4/20/2008
I did all that with this statement :
= "Briefing between "+ Parameters!FromDate.Value+ " and " +Parameters!ToDate.Value
but when i want to dispaly another filter it doesn't show up like this :
= "Briefing between "+ Parameters!FromDate.Value+ " and " +Parameters!ToDate.Value
IIf( Parameters!Company.Value!="",Parameters!Company.Value,"")
I am sorry i have to jump up into Sql reporting service without a good skills on expressions
I'm trying to use the following as an expression in the format of a text box. (Reporting Services 2005)
=System.Text.RegularExpressions.Regex.Replace(First(Fields!JOB_NBR.Value, "main"), "(w{2})(w{4})(w{1})","$1-$2-$3")
however in the edit expression window, I have a red line under the word Replace.
The mouse over tells me it is a "unrecognized identifier"
Any idea's
I need to verify data in a column and do pattern matching on the string in each field.
I've create a CLR Function that will verify the element against the patter and return a True or Fales....
I have only used reg expressions once and am struggling mightly. I'm bacially here. A
I need to match a pattern that each word in the string will be a Capital letter.
ex. The beginning of the day - Fail
ex. The Beginning Of The Day - Pass
[URL] .....
I am writing an SQL query to find an replace data in a column. I have a table that is filled with
C:usersXXXXappdata
C:usersYYYYappda
C:usersZZZZZZappdata
I would like to replace the c:usersXXXXX part with %userprofile%
The end result would be %userprofile%appdata
I know how to dot the replacement in powershell. it's quite easy
-replace "c:users[^]+","%userprofile%"Â Â
Basically how would transfer this into SQL...
hi,
i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files.
the file are named as:
ff_inbound_20071008_1.csv
ff_inbound_20071008_2.csv
where for file ff_inbound_20071008_1.csv:
ff => flat file
inbound => dataset of this csv
20071008 => date
1=> file count
having in mind that they are store in the variable as a full path:
z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv
i need to extract the dataset and the date for each file.
how can i extract these terms from the file name?
many thanks,
nicolas
Thanks in advanace for taking the time to read this post.
I am using MSSQL 2005 and have created a function that allows me to use regular expressions in my SQL queries.
My question is I have a pattern buried in a field of misc data that I need to pull out just that pattern and discard the rest of the data. Here is the Regular Expression I am using
select field1 from table1
where dbo.RegExMatch (field1,'[a-zA-Z]{4}[0-9]{6}[a-zA-Z]{2,4}')=1
This returns all values in the field that match the expression. What I want to do now is remove all data from the field on the left and right of the expression that does not match the expression. How would I accomplish this without reading through the 200k+ records and writing rules for every exception I run across?
so I could have Gar b/a ge 'THE GOOD DATA' m/or1 ba4d da....ta. All I want to do is return 'THE GOOD DATA'
HiI have a large table with allot of data in it, Im trying to find the correct SQL statement to select all the records that do not start with a letter or a number.So some records may start with a '~' and a few '$' and even some '?'. So I would like a statement that would select all of these while leaving out any records that start with anything else, such as numbers or letters.And on a similar theme, Im also trying to select all records that start with a number. Ive been using the "select blah from blah where LIKE 'a%'" for each letter and ive tried to have a play and search around that statement....
Today I was told to use a regex within my 'SELECT' statement, but I had never heard of this being done before... so I done a number of searches on Google and although I found references, I didnt actually find any examples. So when I tried to put something together I kept getting errors.
Ive used regular expressions before so theres no problem writing the expression that I need, however Ive no idea how to implement it into a SELECT statement.
If someone could please show me an example of the structure/syntax of an sql select query that contains a regex then I would be most appreciative! :)
Hello,
Lets say I have a SP that return 1000 records,
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
The full error is this: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
My Code:
SELECT Customers.CustomerID, Customers.EmailAddress, Customers.LastName, Customers.FirstName,
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID),
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID)
FROM Customers JOIN Addresses
ON Customers.CustomerID = Addresses.CustomerID;
I am trying to test a query I am going to use to create a view with all of a customers addresses (billing and shipping).
Hi,
I'm executing the following Query :
SELECT FC3.Quarter, FC3.Service, FC3.TOTALIS, FC2.Quarter AS Expr1, FC2.Service AS Expr2, FC2.FCtIS,
cast((FC2.FCtIS / FC3.TOTALIS) as decimal(4,2)) as Value, getdate() as DateUpdate
--INTO FC4
FROM FC3 LEFT OUTER JOIN
FC2 ON FC3.Quarter = FC2.Quarter AND FC3.Service = FC2.Service
result:
Quarter Service TotalIS Quarter Service FCtIS Value DateUpdate
2007Qt1 Executive contracts 210 2007Qt1 Executive contracts 166 0.00 2007-05-11 15:23:08.100
My problem is that ' can´t get a decimal Value on the VALUE column ....
If i don't use the cast function i get a 0 , forcing a decimal value with cast i get 0.00 ..
This column is intended to retrieve a percentage , like 0,5(50%) ; 0,45 (45%), what is happening ?
I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.
My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.
The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.
How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?
I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
==============================================
Declare @mSql2 Nvarchar(max)
SET @mSql2= (select * from Tablename)
================================================
I'm trying to do something that should be simple. I want to use a copy database task and make it generic so we can call on it from and application which would pass the database names and file locations.
Any examples or documention would be helpful.
The expression edit that is invoked when you click on the ... only allows you to select databasedetails as the object however databasedetails is a collection and I have several properties in that collection that I need to assign values to via variables. I'm still lost and don't know how to enter the expression so that I can set the properties to the values in the variables. In addition the databasedetials collection has a collection which is the databasefiles collection.
Can you show me how to do this? It really is not clear how to assign the values from the variables to the properties in the collection since the editor does not expose the properties with in the property databasedetails since it is a collection.
so How do you enter and expression to set the properties of databasedetails and databasefiles?
DatabaseDetails.DatabaseName?
DatabaseDetails.DatabaseDestinationName?
DatabaseDetails.DatabaseFiles.0.DestinationFilePath?
DatabaseDetails.DatabaseFile.0.SourceFilePath?
DatabaseDetails.DatabaseFile.0.SourceShareFilePath?
DatabaseDetails.DatabaseFiles.1.DestinationFilePath?
DatabaseDetails.DatabaseFile.1.SourceFilePath?
DatabaseDetails.DatabaseFile.1.SourceShareFilePath?
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
Hi, the Microsoft SQL Server version is 2000
Basically I want use a basic regular expression but can't seem to get the syntax right.
i want to compare ID_short = ID_Long
ID_short is a truncated version of ID_Long, and I want to search on the beginning only (hence I can't use the 'LIKE' comparative on it's own).
What is the syntax to use Reg Expressions (or if anyone knows a non RegExp way of searching the beginning please let me know).
Thanks
A developer I need to support believes that the Enterprise Edition is required to take advantage of a dual processor Compaq ProLiant 3000. This machine is not a quad box, just a standard MPS architecture with a single CPU installed.
I recall that the standard SQL 6.5 build will fully utilize a standard MPS DP system and that Enterprise is required only for 4-way, 8-way and n-way boxes such as the Tandems.
Any thoughts?
- Clark
Hi,
I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.
I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?
Thanks,
John
Hi everyone,
Is it possible? I haven't idea how to implement as if it were done from conventional .Net code
I'd like to check every field for a plan file
First field NNNNNNN
Second field XXXNNNNNNN
,,
Thanks for any input or idea,
Hi all,
i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?
here is the C# class that I deploy to a db using VS05.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExTest
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern);
if (r1.Match(matchString.TrimEnd(null)).Success)
{
Regex testReg = new Regex(pattern);
Match regMatch = testReg.Match(matchString);
string runnumber = regMatch.ToString().TrimEnd('_');
runnumber = runnumber.TrimStart('_');
return runnumber;
}
else
{
return null;
}
}
};
Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:
use northwind
go
declare @teststring varchar(50),
@pattern varchar(10),
@out varchar(50)
set @teststring = 'dw_012_34.ext'
set @pattern = '_\d{3}_'
set @out = dbo.RegExMatch(@pattern, @teststring )
select @teststring
select @pattern
select @out
Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?
Help needed please from the SQL team,
I need to import data from one table on to another on a regular basis (twice a day). Sorta like...
From:
Server1
Db1
Table1
To:
Server2
Db1
Table1
I used DTS to get the data initially from Server1 but how I can do it on a automated basis?
Thanks.
I don't like the fact that any user I create has access to system tables inthe Master databases and the ability to issue 'sp_who' plus God knows whatother system stored procs.Is there a white paper or a script to lock down a user so that they do nothave this broad access without breaking their regular ability to run theirown queries against their own databases? I would like them to be a regularuser without the ability to snoop around in my SQL Server.--John Dalberg
View 3 Replies View Related