Is it possible to use the REPLACE function in SQL Server 2000 so that
it returns a string containing only alpha-numeric characters (much
like using regular expressions)?
Thank you in advance for any suggestion.
Darren.
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
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?
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! :)
How can I use regular expressions in WHERE clause directly?
select * from mytbl where myfld like '[0-9]*key'
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
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
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.
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 RelatedI 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
Trying to write the below in an expression box, I still have not figured out an equivalent to €œIN€? in SQL syntax, so far I€™ve had to use €œOR€? to get it to work. Anyone know the right way? I€™m trying to avoid writing this in the stored proc
Example:
Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")
How can you create the infamous INITCAP function to be used in my column transformation expressions?
View 4 Replies View RelatedHi,
I have a table with a field called productname, and it has about 5000 rows, and within that about 1000 have a productname that has 'NIB' in the name, ie "My Product NIB DVD" and I have been asked to replace 'NIB' with 'New' ie "My Product New DVD" Can I do this in SQL using an Update statement? Or do I have build something in maybe asp.net to use a replace function to change the name.
Thanks
I am using MSDE. In this I have a field that contains a desciption. Prior to saving to the db, I replace all vbcrlf's to <br />'s. That works fine when displaying in HTML, but when I display it in a datagrid, I want to replace the <br />'s with vbcrlf's.
I thought I might be able to do it with a replace function in my SQL query, something like "Select ID, replace(Description, '<br />', vbcrlf) as Description". This produces an error that vbcrlf is not a fieldname. So I tried "select replace(Description, '<br />', 'xxx') as Description", and while this did not create an error, neither was the text replaced.
Am I not using the replace function correctly? Is there another way in which I might accomplish the task?
Dim ConnectionString As String = "server='(local)'; trusted_connection=true; database=dbname"
Dim CommandText As String
'Command text is greatly abreviated for this discussion.
CommandText = "Select ID, replace(Description, '<br />', vbcrlf) as Description"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
Dim dv as new dataview()
myCommand.Fill(ds)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
...
...
...
I have a need to evaluate a parameter to use like keywords. (Not my server, so indexing is not available.) I can get the first scenario to work, but wanted to see about getting the syntax so I wouldn't have to exec the statement. If I run the first execute statement, I get three rows (accurate). If I run the second statement, I get no rows. Anybody help me out with the syntax? I've tried so many variations, I'm lost. Many thanks.
declare @key as varChar(50), @sql as varchar(1000)
set @key='flow afow'
set @sql='select myID from myTable where [title] like ''%' + replace(rtrim(ltrim(@key)),' ','%'' or [title] like ''%') + '%'''
exec(@sql)
select myID from myTable where [title] like '%' + replace(@key,' ', '%''' or [title] like '''%') + '%'
I'm trying to replace some text in a field. It looks something like this:
63.73 Avail %= 36.27 Used space MB = 2609.34375 Free space MB = 1485.34765625
I only want the 63.73 number at the beginning. How do I put a wildcard to replace everything after it, starting with 'Avail...'?
I want to perform Replace(searchstring, oldstring, newstring) like function in T-sql as same as in VB..
is there existing one or any other user-defined function?
thanks...
Is it possible to replace 4 different values into one alias column?
ie REPLACE(EligStatus, '0', 'Verified') AS VRFD
..now I need to replace 3 other values with words but i want to keep them in the same VRFD column so I can add it to the report layout.
or is there a better way to do it?
I can't set up a linking table because nowhere in the DB are the values linked to the actual status.
Thanks in advance,
Mark.
Let's try this again.
When I try to use the replace function in my query it ignores the "<" and ">" characters, so that this query:select Description, replace(Description, '<', '* * *') as [Description 1] , replace(Description, 'br /', '? ? ?') as [Description 2] from TT_Projects
produces these results:
Description | Description 1 | Description 2
testsfv <br /> | testsfv <br /> | testsfv <? ? ?> I also tried to replace char(60) with no success.
Any ideas why this is happening?
Hi,
I posted a request here and am still working on it when I landed on this bug.
select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table
The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..
Is this a mssql bug?
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters.
Is there any way to solve my problem??
Jaimin
Hi,Please find the below scenarioOriginal Tablecourse branch_existBY0UI1PO1LI0MK1select REPLACE(branch_exist,1,'yes') as branch from university;displayscourse branch_existBY0UIYesPOYesLI0MKYesWhat i need iscourse branch_existBYNoUIYesPOYesLINoMKYesSql-Server replace function only accepts three arguments, anysuggestions will be greatly welcomed!
View 3 Replies View Related
Hi,
We have this syntax from Microsoft for Replace function:
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
I want to search for two keywords and not only one:
images and images/ (second one is with back slash.
Is it possible to include something like:
images/ OR images in expression2 like
REPLACE ( string_expression1 ,'images/ OR images', string_expression3 )
I want something like that, how do I get that please?
Thank you.
I have 2 tables
1) One With ExchangeRates
a. CurrencyCode ValidFromPeriod Rate
b. EX1 200801 1
c. EX1 200803 2
d. EX1 200805 3
2) One with just OrderHeaders
a. ID CurrencyCode Period
b. 1 EX1 200801
c. 2 EX1 200802
d. 3 EX1 200803
e. 4 EX1 200804
f. 5 EX1 200805
The Idea is to link these orderheaders with their correct ExchangeRate. Which I only managed to do by using a function. Is there a beter (faster) way to do it?
Code Snippet
SELECT OrderHeaders.ID, OrderHeaders.CurrencyCode, OrderHeaders.Period, dbo.GetExchangeRate(OrderHeaders.CurrencyCode, OrderHeaders.Period) AS ExchangeRate FROM OrderHeaders
FUNCTION GetExchangeRate
@CurrencyCode VarChar(3),
@Period int
BEGIN
DECLARE @VarResult Float
SET @VarResult =0
SELECT TOP 1 @VarResult=Rate FROM ExchangeRates WHERE CurrencyCode=@CurrencyCode AND ValidFromPeriod<=@Period ORDER BY ValidFromPeriod DESC
Return @VarResult
END
heyi'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like soSET @prOtherValue = REPLACE(@prOtherValue,''', '''')this doesn't work thowhats the work aroundcheers!!!!
View 6 Replies View Relateddoes anyone know where i can find a user defined function that replaces accented characters with their normal ones
i want to replace all accented characters since for some reason sql server does not store the characters properly, or when they exported to excel for html they are displayed incorrectly
thanks
i have column in table which contains tabs and " i want replace with space...i am using repalce function is thier other way to improve performance with out using replace function.
View 9 Replies View RelatedUpdate query to modify a Url in the text column with another url
Ex:
Col1Â Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/10325
3Â This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/10118
10Â This alert fired to indicated that error with
https://abc.def.com/default/_workitem/10150
to
Col1 Col2
1 An unexpected event occurred
https://abc.def.com/default/_workitem/11111
3 This alert occurs when service jobs run on
https://abc.def.com/default/_workitem/11111
10 This alert fired to indicated that error with
https://abc.def.com/default/_workitem/11111
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
I'm looking for a way to pull off a complex find+replace within some code, as follows:
@step_name = N'SAME - OCF Collins (Tabard)', @command = N'DTSRun /~Z0x5F4F7B0688825E7544AC46CFD664F98AC ', @database_name = N'
We have over 200 variants of the above, but following the same syntax (@step_name, Dbname, @Command etc...
Rules:
1) Note the unique identifier "~Z0x5F4F7B0688825E7544AC46CFD664F98AC". I would like it replaced for whatever is between "@step_name = N'" , and @command = N'DTSRun ; (this will form a filename).
2) Note the 'DTSRun /' string. I'd like that replaced with Dtexec /F "D:MyFileLocationFolderHere" (this folder remains constant).