SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove Pattern in sql query


Remove Pattern in sql query

Author
Message
niteshrajgopal
niteshrajgopal
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 336
hi guys,

this is my first post here:-D. Hope someone can help me. I have a field in my table with data that looks like this:

PO_x0020_416G_x002F_484A
S_x0020_8292

I need to strip out the _x002F_ and x_0020_ etc and the final result must look like this:

PO 416G 484A
S 8292

Please help me write a function to do this. Thanks in advance
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
Nitesh, apart from _x002F_ and x_0020_ , are there any other pattern that might come in the string??
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
Also as u are brand new to this forum, i would recommend you going through this following article :-)
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN

so please post:

1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.

When u do so, i am sure a lot of us will help u instantly...

C'est Pras!
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
If '_x0020_' and '_x002f_' are going to be the only patterns that need to be replaced, then this following code with do the trick for you!

First, check out how i set up the environment by providing the Tables and the Sample data!

IF OBJECT_ID('TEMPDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table
(
String VARCHAR(128)
)

INSERT INTO #Table (String)
SELECT 'PO_x0020_416G_x002F_484A' UNION ALL
SELECT 'S_x0020_8292' UNION ALL
SELECT 'ABC_x0020_DEF_x0020_GHI_x002f_' UNION ALL
SELECT 'A1B2_x0020_D3E44_x0020_5FR'



Now for the code that will strip '_x0020_' and '_x002f_' from the string column:

SELECT REPLACE(REPLACE(String,'_x0020_',' '),'_x002f_',' ') REPLACED_DATA FROM #TABLE



Hope this gets you started. If not, then we are awating your clear requirements!

Cheers!
niteshrajgopal
niteshrajgopal
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 336
thanks for the replies guys. sorry about not following etiquette.

there could be other patterns like _x0002c etc. So I can't really do a replaceSad
niteshrajgopal
niteshrajgopal
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 336
guys I've done this scalar function which gives me the first and last part of the string:

for example P0_x002_3345

will give me PO3345

but for ones like this:

PO_x002e_5567_3333

I need to get PO 5567 3333

and I only get

PO3333

ALTER function [dbo].[ExtractPONumber](@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Part1 varchar(8000);
declare @Part2 varchar(8000);

if LEN(@Text) > 0
begin
while (PATINDEX('%x002%',@Text) > 1)
begin
set @Text =
replace(@Text,
'x002',
'')


end

set @Part1 = CASE WHEN CHARINDEX('_',@Text) > 0 THEN LEFT(@Text,CHARINDEX('_',@Text)-1) ELSE NULL END
--LEFT(@Text,CHARINDEX('_',@Text)-1)
set @Part2 = REPLACE(@Text,@Part1,'')
set @Part2 = REVERSE(@Part2)
set @Part2 = CASE WHEN CHARINDEX('_',@Part2) > 0 then LEFT(@Part2, CHARINDEX('_',@Part2)-1) else @Part2 END
set @Part2 = REVERSE(@Part2)
--set @Text = @Part1 + REPLACE(@Part2,'_','')
end
return @Part1 + @Part2--REPLACE(@Part1 + @Part2,'_',' ')
end


ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
Nitesh, another question, does your pattern always start and end with underscore ( _ ) ?
niteshrajgopal
niteshrajgopal
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 336
Hi ColdCoffee,

yes it does always start and end with an _ .
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
Nitesh, here is a function that i coded for your requirement. I have used WHILE loop which is RBAR practice. There are lot of set-based code for this, which i am unaware and un-tried off. Probably lets wait for the others to jump in a provide an absolute fast code. Until then, this code wud suffice your needs!

Check it out: Inline with the sample data i have give, this will work out for you

IF OBJECT_ID (N'dbo.Replace_A_Pattern', N'FN') IS NOT NULL
DROP FUNCTION dbo.Replace_A_Pattern;
GO

CREATE FUNCTION dbo.Replace_A_Pattern(@ToBeReplacedString VARCHAR(128),@FromString VARCHAR(128))
RETURNS VARCHAR(128)
AS
BEGIN
DECLARE @START INT
DECLARE @END INT
DECLARE @LENGTH INT

SET @START = 1
SET @END = DATALENGTH(@FromString)
SET @LENGTH = DATALENGTH(@ToBeReplacedString)

WHILE @START <= @END
BEGIN
DECLARE @POS INT
SET @POS = CHARINDEX(@ToBeReplacedString,@FromString)

IF @POS > 0
/* The hardcoding of 7 in following STUFF function is for your requirement
for general purpose, it has to be @LENGTH variable
*/
SELECT @FromString = STUFF(@FromString , @POS , 7,' ')

SET @START = @START + 1
END

RETURN @FromString
END;
GO



Now , execute the funtion:

SELECT String,dbo.Replace_A_Pattern('_x002',String) Stripped_String FROM #Table



Now lets check out the results along with the input string:

String Stripped_String
---------------------- --------------
PO_x0020_416G_x002F_484A PO 416G 484A
S_x0020_8292 S 8292
ABC_x0020_DEF_x0020_GHI_x002f_ ABC DEF GHI
A1B2_x0020_D3E44_x0020_5FR A1B2 D3E44 5FR


Hope this helps you! Tell us if this code did well!

Cheers!
niteshrajgopal
niteshrajgopal
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 336
thank you so much ColdCoffee. This function is absolutely perfect for my requirement. and it is quite quick. 4 secs for 1000 records. you are a master;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search