Eliminate characters from a string based on anothe

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)

Eliminate characters from a string based on anothe

Author

Topic

rajan.nithin
Starting Member

42 Posts

Posted - 2014-12-17 : 07:55:38

I have a string 'ACDIPFJZ'

In my table one of the column has data like

PFAG
ABCDEFHJMPUYZ
KML
JC
RPF

My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.

My output should be:

PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 09:28:23

quote:

Originally posted by gbritton

This worked for me:


declare @t table (a varchar(20)) 
insert into @t values
('PFAG			'),
('ABCDEFHJMPUYZ	'),
('KML			'),
('JC			'),
('RPF			')

declare @s varchar(20) = 'ACDIPFJZ';

with n1(n) as (select 1 from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) v(n)),
     n2(n) as (select 1 from n1, n1 n),
     n4(n) as (select 1 from n2, n2 n),
     n8(n) as (select 1 from n4, n4 n),
     N(n)  as (select top (len(@s)) row_number() over(order by (select 1)) from n8)

select a, d.x.value('.', 'nvarchar(20)') from @t

cross apply (
	select '' + substring(a,charindex(substring(@s, n, 1), a), 1)
	from N
	where charindex(substring(@s, n, 1), a) > 0
	for xml path(''),type
	) d(x)
where d.x is not null
order by a

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-17 : 12:20:10

If the data string is reasonably short -- no more than 30 bytes -- I'd skip the tally table and XML and do this directly:



declare @table table (data varchar(20))
insert into @table values
('PFAG			'),
('ABCDEFHJMPUYZ	'),
('KML			'),
('JC			'),
('RPF			')

DECLARE @string varchar(20) = '[ACDIPFJZ]';

SELECT
    data AS original_data,
    CASE WHEN SUBSTRING(data, 01, 1) LIKE @string THEN SUBSTRING(data, 01, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 02, 1) LIKE @string THEN SUBSTRING(data, 02, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 03, 1) LIKE @string THEN SUBSTRING(data, 03, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 04, 1) LIKE @string THEN SUBSTRING(data, 04, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 05, 1) LIKE @string THEN SUBSTRING(data, 05, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 06, 1) LIKE @string THEN SUBSTRING(data, 06, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 07, 1) LIKE @string THEN SUBSTRING(data, 07, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 08, 1) LIKE @string THEN SUBSTRING(data, 08, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 09, 1) LIKE @string THEN SUBSTRING(data, 09, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 10, 1) LIKE @string THEN SUBSTRING(data, 10, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 11, 1) LIKE @string THEN SUBSTRING(data, 11, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 12, 1) LIKE @string THEN SUBSTRING(data, 12, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 13, 1) LIKE @string THEN SUBSTRING(data, 13, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 14, 1) LIKE @string THEN SUBSTRING(data, 14, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 15, 1) LIKE @string THEN SUBSTRING(data, 15, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 16, 1) LIKE @string THEN SUBSTRING(data, 16, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 17, 1) LIKE @string THEN SUBSTRING(data, 17, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 18, 1) LIKE @string THEN SUBSTRING(data, 18, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 19, 1) LIKE @string THEN SUBSTRING(data, 19, 1) ELSE '' END +
    CASE WHEN SUBSTRING(data, 20, 1) LIKE @string THEN SUBSTRING(data, 20, 1) ELSE '' END
        AS modified_data
FROM @table

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-17 : 14:08:43

yup. sometimes brute force is best!

rajan.nithin
Starting Member

42 Posts

Posted - 2014-12-18 : 01:09:29

Thanks a lot for the solutions !!!

One of my friend came up with this solution :

declare @ValidChars as varchar(13) = '%[^ACDIPFJZ]%';
declare @TestString as varchar(20) = 'ABCDEFHJMPUYZ';

while PatIndex(@ValidChars,@TestString) > 0
set @TestString = STUFF(@TestString, PatIndex(@ValidChars, @TestString),1,'')

select @TestString;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 09:13:58

I try to avoid loops and cursors like that. Usually they come with a performance penalty.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-18 : 11:40:18

Along with that, it's very difficult to include the WHILE loop in a SELECT :-).

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 11:50:46

No kidding. I think the use of the while loop would require the use of a cursor.

double trouble!