Remove zero's from string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Remove zero's from string

Author

Topic

bkokster
Starting Member

3 Posts

Posted - 2014-03-12 : 09:05:05

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 - 2014-03-12 : 09:59:03

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-12 : 11:53:26

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

bkokster
Starting Member

3 Posts

Posted - 2014-03-13 : 01:42:02

Thanks Mik, makes sense!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-14 : 16:52:15

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources