SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Remove zero's from string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bkokster
Starting Member

3 Posts

Posted - 03/12/2014 :  09:05:05  Show Profile  Reply with Quote
Hi,

I'm busy writing a function that removes space delimited elements consisting only of zeros from a string.

For example '00 0012 abcd 0000000000' should be converted to '0012 abcd'.

My current approach is to consider each space delimited element of the string and check whether the element contains anything other than a 0, in which case I'll retain it. Alternatively I use the stuff function to remove the string of 0's.

This will be used to process over a 150 million strings once off and more or less 2 million strings on a regular basis, so my current approach is quite resource intensive. Can someone perhaps suggest a more elegant/efficient approach?

Thanks in advance!
Benji

bkokster
Starting Member

3 Posts

Posted - 03/12/2014 :  09:59:03  Show Profile  Reply with Quote
Here's the funtion that I'm using, any ideas on how to make it faster?

CREATE FUNCTION REMOVE_ZERO_NUMBERS(@INPUT_STRING VARCHAR(150))
RETURNS VARCHAR(150)
AS
BEGIN

DECLARE @NEWSTRING VARCHAR(150),@START INT, @LENGTH INT, @STRINGPART VARCHAR(150)
SET @NEWSTRING = ''
SET @START = 1
SET @LENGTH = CASE WHEN PATINDEX('% %',@INPUT_STRING) = 0 THEN LEN(@INPUT_STRING) ELSE PATINDEX('% %',@INPUT_STRING) END

WHILE @START <= LEN(@INPUT_STRING)
BEGIN

SET @STRINGPART = SUBSTRING(@INPUT_STRING,@START,@LENGTH)

IF PATINDEX('%[^0 ]%',@STRINGPART) <> 0 SET @NEWSTRING = @NEWSTRING + @STRINGPART

SET @START = @START + @LENGTH
SET @LENGTH = CASE

WHEN PATINDEX('% %',SUBSTRING(@INPUT_STRING,@START,LEN(@INPUT_STRING) - @LENGTH + 1)) = 0 THEN LEN(@INPUT_STRING) - @LENGTH + 1
ELSE PATINDEX('% %',SUBSTRING(@INPUT_STRING,@START,LEN(@INPUT_STRING) - @LENGTH + 1))

END

END
RETURN @NEWSTRING

END
GO
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/12/2014 :  11:53:26  Show Profile  Reply with Quote
well my approach would be to

1) convert the space separated strings into tabular form and store it in a table. You may need to have a split function for this purpose - google and you'll get one ready.

2) then look for specific elements in that tabular form data, which you think should be part of projected value. In the specified case once the values are converted into tabular form, it'll look like following
declare @table table (string varchar(100))
insert into @table values ('00'),('0012'),('abcd'),('0000000000')
select * from @table

and then, you'll need to find out the desired record - following is how you may determine which parts needs to be in your projection

select * from @table WHERE String like '%[1-9]%' OR String like '%[a-z]%' -- this will give you only "0012" and "abcd". but note that the where condition may differ if there is any variation in your data

3) once you selected the desired result, convert it the tabular values back into space separated string.
e.g.

declare @table table (string varchar(100))
insert into @table values ('00'),('0012'),('abcd'),('0000000000')
select * from @table
SELECT STUFF(
(SELECT ' '+string
FROM @table X
WHERE string like '%[1-9]%' OR string like '%[a-z]%'
For XML Path(''))
,1,1,'')



Cheers
MIK
Go to Top of Page

bkokster
Starting Member

3 Posts

Posted - 03/13/2014 :  01:42:02  Show Profile  Reply with Quote
Thanks Mik, makes sense!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
412 Posts

Posted - 03/14/2014 :  16:52:15  Show Profile  Reply with Quote
I would think that any splitting and recombining of strings would be a lot of overhead.

Maybe try my version of a custom function instead, just to see how it performs. I've done my own testing, but naturally you'll also need to verify that it works correctly for all your input :-).


ALTER FUNCTION REMOVE_ZERO_NUMBERS(@INPUT_STRING VARCHAR(150))
RETURNS VARCHAR(150)
AS
BEGIN
    
    SET @INPUT_STRING = ' ' + @INPUT_STRING + ' '

    DECLARE @START_OF_ZERO_STRING INT
    DECLARE @BYTE_OF_SPACE_AFTER_STRING INT

    SET @START_OF_ZERO_STRING = PATINDEX('% 0%0 %', @INPUT_STRING) + 1
    WHILE @START_OF_ZERO_STRING > 0 AND @START_OF_ZERO_STRING <= LEN(@INPUT_STRING)
    BEGIN
        SET @BYTE_OF_SPACE_AFTER_STRING = CHARINDEX(' ', @INPUT_STRING, @START_OF_ZERO_STRING + 1)
        /* debugging-only code
        PRINT '123456789!123456789@123456789#123456789$123456789%123456789^123456789&123456789*'
        PRINT @INPUT_STRING + '~' + ' ' + CAST(@START_OF_ZERO_STRING AS varchar(10)) + '/' + CAST(@BYTE_OF_SPACE_AFTER_STRING AS varchar(10))
        SELECT 
            @INPUT_STRING AS INPUT_STRING,
            @START_OF_ZERO_STRING AS START_OF_ZERO_STRING, 
            @BYTE_OF_SPACE_AFTER_STRING AS BYTE_OF_SPACE_AFTER_STRING,
            PATINDEX('%[^0]%', SUBSTRING(@INPUT_STRING, @START_OF_ZERO_STRING + 1, 150)) AS [PATINDEX]
        */  
        IF @BYTE_OF_SPACE_AFTER_STRING < (@START_OF_ZERO_STRING + 1 + 
        PATINDEX('%[^0]%', SUBSTRING(@INPUT_STRING, @START_OF_ZERO_STRING + 1, 150)))
        BEGIN
            SET @INPUT_STRING = STUFF(@INPUT_STRING, @START_OF_ZERO_STRING - 1, @BYTE_OF_SPACE_AFTER_STRING - @START_OF_ZERO_STRING + 1, '')
        END --IF
        ELSE
            SET @START_OF_ZERO_STRING = @BYTE_OF_SPACE_AFTER_STRING

        SET @START_OF_ZERO_STRING = PATINDEX('% 0%0 %', SUBSTRING(@INPUT_STRING, @START_OF_ZERO_STRING, 150)) + 
            @START_OF_ZERO_STRING
    END --WHILE
	RETURN @INPUT_STRING
END --FUNCTION
GO

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000