February 12, 2014 at 7:25 am
Hello Everyone
I need to develop logic to remove number from logic.
see below for DDL
CREATE TABLE #TEMP
(Code VARCHAR(20))
INSERT INTO #TEMP VALUES ('3MTWRF(2)')
INSERT INTO #TEMP VALUES ('1-2MTWRF(1)')
INSERT INTO #TEMP VALUES ('1MTW1-2RF(Y)')
INSERT INTO #TEMP VALUES ('5MT(Y)')
INSERT INTO #TEMP VALUES ('9MTW(1)')
so currently table has below values
Code
3MTWRF(2)
1-2MTWRF(1)
1MTW1-2RF(Y)
5MT(Y)
9MTW(1)
desire output is
Code
MTWRF
MTWRF
MTWRF
MT
MTW
Please help me to develop the logic.
Thanks
February 12, 2014 at 7:35 am
I'd create a little scalar function, should work like a charm:
CREATE FUNCTION [dbo].[fx_RemoveCharsFromString] (
@String nvarchar(max),
@Match varchar(255)
)
/*
----------------------------------------------------------------------------------------------------------------
Purpose: Removes unwanted characters from any given string
----------------------------------------------------------------------------------------------------------------
NOTES:Return numeric only:@Match = '^0-9'
Return alphabetic only:@Match = '^a-z'
Return alphnumeric only:@Match = '^a-z0-9'
Return non-alphnumeric only:@Match = 'a-z0-9'
----------------------------------------------------------------------------------------------------------------
SELECT dbo.fx_RemoveCharsFromString('1MTW1-2RF(Y)', '^a-z')
*/
RETURNS nvarchar(max) AS
BEGIN
SET @Match = '%['+@Match+']%'
WHILE PatIndex(@Match, @String) > 0
SET @String = Stuff(@String, PatIndex(@Match, @String), 1, '')
RETURN @String
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 12, 2014 at 8:41 am
Scalar functions of this sort are not very good performers...
Any way looks like OP has quite a specific rules, not just take non-alpha characters away. He also wants to take only alphachars before left-bracket...
select t.code, (select '' + l
from (select top (case when charindex('(',t.code)>0 then charindex('(',t.code)-1 else len(t.code) end) row_number() over (order by (select null)) n from sys.columns) a
cross apply (select case when ASCII(UPPER(substring(t.code,a.n,1))) != ASCII(LOWER(substring(t.code,a.n,1))) THEN substring(t.code,a.n,1) else '' end l) l
for xml path ('')) decoded
from #TEMP t
The above can be written as iTVF, which would not compromise performance.
Instead of sys.columns, you better to use some dedicated tally table (search fot tally table on this site for more details).
February 12, 2014 at 8:51 am
MyDoggieJessie (2/12/2014)
I'd create a little scalar function, should work like a charm:
I wouldn't because it won't, it will just cause performance issues.
I'd create an inLine Table-valued function.
CREATE FUNCTION dbo.ReturnOnlyChars(
@String varchar(8000)-- = '1MTW1-2RF(Y)';
)
RETURNS TABLE AS
RETURN
WITH E1(N) AS(
SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E(N) --10 rows
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b --10*10 = 100 rows
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b --100*100 = 10,000 rows
),
cteTally AS(
SELECT TOP(LEN(@String)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) N
FROM E4
)
SELECT CAST((SELECT SUBSTRING( @String, N, 1)
FROM cteTally
WHERE SUBSTRING( @String, N, 2) LIKE '[A-Z][^)]' --Only chars avoids (Y) values
FOR XML PATH('')) AS VARCHAR(8000)) String
And you just call it like this:
SELECT String
FROM #TEMP
CROSS APPLY dbo.ReturnOnlyChars(Code)
If you have questions about how it works, feel free to ask
February 12, 2014 at 8:52 am
Nice alternative Eugene, I like it!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply