Extract Substring From String(Regular Expressions)
Feb 11, 2008
Hello forum, I need extract a substring form a string that follows a regular expressions -->
T:1º/PQ:1/TALLA:2(MOD.51 100/150)
T:<number>º/PQ:<number>...
I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...
I would like me use it as:
SELECT RegularExpression (Field)
Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.
View 3 Replies
ADVERTISEMENT
Jul 9, 2014
I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
View 9 Replies
View Related
Aug 5, 2013
SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
View 4 Replies
View Related
Jun 24, 2008
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
View 5 Replies
View Related
Feb 9, 2007
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
View 4 Replies
View Related
Sep 27, 2007
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,
View 11 Replies
View Related
Sep 24, 2006
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?
View 8 Replies
View Related
Apr 27, 2007
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! :)
View 2 Replies
View Related
Jul 20, 2005
Is it possible to use the REPLACE function in SQL Server 2000 so thatit returns a string containing only alpha-numeric characters (muchlike using regular expressions)?Thank you in advance for any suggestion.Darren.
View 1 Replies
View Related
Oct 30, 2006
How can I use regular expressions in WHERE clause directly?
select * from mytbl where myfld like '[0-9]*key'
View 3 Replies
View Related
Mar 31, 2006
Hi-
Someone posted on a thread that you can use VB (or C#) to write Regular Expressions for SQL Server 2005. I know how to write RegEx in VB, so can someone point me to a reference which explains how to use them in / with SQL2005? Or, can someone explain how?
Thanks,
-David
View 4 Replies
View Related
Apr 20, 2007
I am running PHP with SQL Server 2005. I have [bold] in some of the values in my result set, which needs to be striped out. I had a function in SQL Server2000 that did that, but the same function does not work in SQL Server2005. Any dieas?
CREATE FUNCTION
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
set @match=0
exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OADestroy @obj
return @match
END
GO
View 7 Replies
View Related
Mar 23, 2006
Hi,I trying to write a select statement that will return each of my salesmen a region code based on a table of post codes using wildcards... eg.MK1 1AA would be matched in the region code table to MK1%SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,rc.POST_CODE, dn.POSTAL_CODEFROM REGIONAL_CODES rc CROSS JOINDEALER_NAW dnWHERE (dn.POSTAL_CODE LIKE rc.POST_CODE)The above statement works BUT there are some post code areas such asour friends in Milton Keynes that are split into two regions... eg MK1is region id 2 and MK10 is region 3.So a dealer with post code MK10 1AA would be matched to both rowsreturning duplicatesPOST_CODE REGION_IDMK1% 2MK10% 3I think the answer would lie in a subquery which returns the ID of theregion with the longest length of the postcode match (e.g.len(POST_CODE) for the rc table... return only the MAX....any ideas????Any help muchos appreciated, and I apologies now for the naming of thedealers name as a reserve word... not me!Ct
View 2 Replies
View Related
Feb 12, 2015
I tried to extract the information in a field
Samples of the data:
"Current Date updated ( 12/31/2015 -> 12/31/2016 )"
"Status updated ( Open -> Should be Closed )"
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"
These are what all records look like:
1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"
View 1 Replies
View Related
Apr 23, 2015
I have two columns "Project Name" and "Flag".
Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.
View 15 Replies
View Related
Mar 1, 2014
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] .....
View 2 Replies
View Related
Oct 15, 2007
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
View 4 Replies
View Related
Oct 17, 2007
i'm using sql server 2005 and i need the sql i can use to select rows where the string contains a substring (in access i used instr but now it tells me it's not a built-in function.
View 4 Replies
View Related
Jan 28, 2008
I have a column name URL in Table1 with data like <a href="/Folder1/view_media_news.cfm?news_media_i=1">August 2002 Factsheet</a> <a href="/Folder1/view_media_news.cfm?news_media_i=149">March 2002 Newsletter </a> i need to grab the news_media_i value by sql query Please any one can help me to get that particular value from string using substring or any other suggestion Thank you in advance
View 4 Replies
View Related
Apr 29, 2008
Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields:
select UserName from MyDirectory
NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos
How can I fix this query so that it returns everything to the right of the '' character?
So I want the query to instead return the following:
Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
View 2 Replies
View Related
Aug 26, 2014
Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
I've tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally...
View 8 Replies
View Related
Feb 19, 2014
I have a table which has important information stored within a text field as nText. The fields look like this
{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}
The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?
I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.
,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)
I use Microsoft SQL 2008
View 6 Replies
View Related
May 17, 2015
I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?
INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID)
SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID
WHERE ...
GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
View 2 Replies
View Related
Sep 9, 2005
Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies
View Related
Jun 17, 2014
I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.
Begin Quote Calculation<br />
<br />....<br />
Agent Id: 001<br />
Trip Duration: 5days<br />
Relationship Type: Individual<br />....nDestination: AreaTwo<br />
<br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 9.070000<br />.....
Result
Trip Duration: 5 days
Destination: AreaTwo
Base Rate: 6.070000
View 3 Replies
View Related
Jul 23, 2005
Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.
View 4 Replies
View Related
Aug 22, 2007
Hi,
I have 2 questions.
1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
The length of "Name" varies and I want to extract all characters included for "Name".
Can anyone advise what the query/SQL statement would look like?
2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.
So I want to include a code that checks if file "file.xls" is open on user's machine.
If file is open, then message "file "File.xls" is already open. Generating File_1.xls"
Run the code but create the file with file name "file_1.xls"
If file doesn't exist, then run code and create file with file name "File.xls"
Please advise.
View 8 Replies
View Related
May 4, 1999
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
View 10 Replies
View Related
Aug 29, 2013
Creating a stored procedure in extracting a word in a sentence.
Example:
String=The quick brown fox JuMp over the lazy dog.
I want to extract a random word on the string.
View 1 Replies
View Related
Jul 20, 2005
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
View 4 Replies
View Related
Oct 15, 2015
I have a values a column like this
Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)
So i just only need 07:00-16:30 this kind of part from above all these string in sql server.
View 4 Replies
View Related
Nov 2, 2015
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
View 4 Replies
View Related
May 11, 2015
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
View 2 Replies
View Related
