Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Remove Pattern in sql query Expand / Collapse
Author
Message
Posted Monday, May 17, 2010 3:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:36 AM
Points: 54, Visits: 327
hi guys,

this is my first post here. 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
Post #922765
Posted Monday, May 17, 2010 3:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Nitesh, apart from _x002F_ and x_0020_ , are there any other pattern that might come in the string??
Post #922770
Posted Monday, May 17, 2010 3:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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!
Post #922771
Posted Monday, May 17, 2010 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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!
Post #922773
Posted Monday, May 17, 2010 4:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:36 AM
Points: 54, Visits: 327
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 replace
Post #922793
Posted Monday, May 17, 2010 4:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:36 AM
Points: 54, Visits: 327
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

Post #922795
Posted Monday, May 17, 2010 4:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Nitesh, another question, does your pattern always start and end with underscore ( _ ) ?
Post #922797
Posted Monday, May 17, 2010 4:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:36 AM
Points: 54, Visits: 327
Hi ColdCoffee,

yes it does always start and end with an _ .
Post #922799
Posted Monday, May 17, 2010 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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!
Post #922932
Posted Tuesday, May 18, 2010 12:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 12:36 AM
Points: 54, Visits: 327
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
Post #923319
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse