December 17, 2014 at 5:22 am
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)
December 17, 2014 at 7:31 pm
My guess is there's probably a better way to formulate the problem you're trying to solve so that this isn't required, but for the sake of argument, lets say that it is. If doing it outside of SQL is not an option, I'd split out all the string (in this case, characters) you want to check into their own table.
Then join that table of strings to check to the set of stuff you want to validate. If you just wanted to check existance, the join itself would be sufficient. But if you also need a string containing those characters, you could use FOR XML to concatenate the strings you found back into a coherant string.
This code assumes you have a string splitting function called dbo.SplitStrings_CLR. If you don't, any string splitting funciton will work, so long as it gets the characters you want into @StringsToCheck.
--Set up a table of the strings (in your case, just CHARACTERS) you want to chek for
declare @StringsToCheck table
(
String varchar(30) primary key clustered
)
--Represents the table you want to search strings for
declare @ValidationSet table
(
CheckedString varchar(500)
)
--Replace this with whatever you need to insert each string into @StringsToCheck
--
insert into @StringsToCheck (String)
select Item
from dbo.SplitStrings_CLR('A,C,D,I,P,F,J,Z', ',')
-- Populate sample data you want to check
insert into @ValidationSet(CheckedString)
values ('PFAG'),('ABCDEFHJMPUYZ'),('KML'),('JC'),('RFP')
;with t as
(
select a.String,b.CheckedString
from @StringsToCheck a
inner join @ValidationSet b
on b.CheckedString like '%' + a.String + '%' --This checks if the string appears anywhere in b.CheckedString
--Note this CANNOT make use of indexes
)
--Re-concatenate the found characters into a coherant string
select
checkedString,
(select string + ''
from t i --Inner
where i.CheckedString = o.CheckedString
for xml path('')) as CoherantString
from t o --outer
group by checkedString
Note that this probably doesnt scale up very well, but if thats an issue, again, there may be a completely different way to attack whatever it is you're trying to do.
December 17, 2014 at 11:07 pm
Thanks a looot for the solution !!!:-):-):-)
December 18, 2014 at 3:49 am
Depending on how your data is set up, this might work for you too.
DECLARE @Chars2Remove VARCHAR(20) = 'ACDIPFJZ';
WITH RowsToCheck AS
(
SELECT s
FROM
(
VALUES ('PFAG'),('ABCDEFHJMPUYZ'),('KML'),('JC'),('RFP')
) a (s)
),
Tally (n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT s, snew=
(
SELECT s2 + ''
FROM RowsToCheck a
CROSS APPLY
(
SELECT TOP (LEN(s)) n, SUBSTRING(s, n, 1)
FROM Tally
) b (n, s2)
LEFT JOIN
(
SELECT s3
FROM (SELECT @Chars2Remove) c (s)
CROSS APPLY
(
SELECT SUBSTRING(s, n, 1)
FROM Tally
WHERE n BETWEEN 1 AND LEN(s)
) d (s3)
) c ON s2 = s3
WHERE s3 IS NOT NULL AND a.s = x.s
ORDER BY s, n
FOR XML PATH('')
)
FROM RowsToCheck x;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply