May 17, 2010 at 3:20 am
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
May 17, 2010 at 3:30 am
Nitesh, apart from _x002F_ and x_0020_ , are there any other pattern that might come in the string??
May 17, 2010 at 3:33 am
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[/url]
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!
May 17, 2010 at 3:41 am
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!
May 17, 2010 at 4:37 am
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:(
May 17, 2010 at 4:43 am
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
May 17, 2010 at 4:50 am
Nitesh, another question, does your pattern always start and end with underscore ( _ ) ?
May 17, 2010 at 4:55 am
Hi ColdCoffee,
yes it does always start and end with an _ .
May 17, 2010 at 8:28 am
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!
May 18, 2010 at 12:44 am
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;-)
May 18, 2010 at 2:09 am
You're welcome, nitesh..:-)
May 18, 2010 at 2:32 am
Hi again, is there anyway I can add to your reputation or something on this forum:-)
May 18, 2010 at 2:54 am
I'm afraid we dont have any..
May 18, 2010 at 5:38 am
niteshrajgopal (5/18/2010)
Hi again, is there anyway I can add to your reputation or something on this forum:-)
Heh... not to worry. CC is building his reputation where it counts. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2010 at 8:48 pm
{edit} Sorry... had a bug in the code and had to remove this post.
{edit} {edit} Heh... it turned out to not be a bug. The Tally table code ran so fast I didn't think that it actually ran. 😀 It surprised even me. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply