September 21, 2012 at 5:11 am
Hi,
Please let me know how to retrieve the number from Attribute column based on IndexKey using user defined function.
create table Test_FindIndexNumber
(AttributeKey varchar(100)null ,
DisplayIndexNumber int null)
insert into Test_FindIndexNumber(AttributeKey,IndexKey,DisplayIndexNumber)
values ('A.B(1).C(1).D(10)','A.B(n).C(n)',null)
,('A.B(2).C(100).D(1000).F(333)','A.B(n).C(n)',null)
,('A.B(3).C(20).D(40).F(333).E(100)','A.B(n).C(n)',null)
,('A.B.C.D(10).F(100)','A.B.C.D(n)',null)
,('A.B.C.D(1000).K(333)','A.B.C.D(n)',null)
,('A.B.C.D(40).K(22).E(100)','A.B.C.D(n)',null)
EXAMPLE-I:
Index Key : A.B(n).C(n)
DisplayIndexNumber: A.B(1).C(1).D(10) is 1
DisplayIndexNumber : A.B(2).C(100).D(1000).F(333) is 100
DisplayIndexNumber : A.B(3).C(20).D(40).F(333).E(100) is 20
EXAMPLE-II:
Index Key : A.B.C.D(n)
DisplayIndexNumber : A.B.C.D(10).F(100) is 10
DisplayIndexNumber : A.B.C.D(1000).K(333) is 1000
DisplayIndexNumber : 'A.B.C.D(40).K(22).E(100) is 40
Generate DisplayIndexNumber using 'n' from indexkey when applied to Attribute is the output for all cases
Please help me.
Thanks,
Kumar.
September 21, 2012 at 5:20 am
;with smpl
as
(
select 'A.B.C(1).D(10)' IK
union select 'A.B.C(100).D(1000).F(333)'
union select 'A.B.C(20).D(40).E(100)'
)
select SUBSTRING(IK,7,CHARINDEX(')',IK)-7) from smpl
Please follow the link in the bottom of my signature for explanation and details 😉
September 21, 2012 at 5:24 am
Hi,
I am expecting a generic solution so that even if the attributes change,it should work
September 21, 2012 at 5:56 am
ssskumar4u (9/21/2012)
Hi,I am expecting a generic solution so that even if the attributes change,it should work
Hi,
And I am expecting you to read and follow forum "how to post questions" guide. So, no-one need to waste their time on guessing requirement details and setting up data for try and test.
Link at the bottom of my signature for your convenience.
September 21, 2012 at 6:21 am
Hi,
I have updated my problem statement as per given guidelines. Hope it is fine now.
Thanks
September 21, 2012 at 8:14 am
Ok, this kind of work is not for T-SQL to do.
You will be best to create CLR function for this. Your IndexKey is not really a Key, but a pattern.
In CLR you can use RegEx to achieve what you want in much more sufficient way than in T-SQL.
You may find some people here, who will take this as some kind of challenge and may come up with T-SQL way of solving it, but again - this is not what T-SQL is for.
The closest analogue of RegEx in T-SQL is PATINDEX function, but it has very limited functionality and it will not be easy to apply flexible patters of sort you have.
September 21, 2012 at 8:28 am
ssskumar4u (9/21/2012)
Hi,I have updated my problem statement as per given guidelines. Hope it is fine now.
Thanks
I totally agree with Eugene. I thought I might have a go at it just for fun but your ddl is not valid. Your insert statement has more columns than the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 8:39 am
CREATE FUNCTION dbo.f_OhMyGod (@str varchar(100), @pattern varchar(100)) RETURNS VARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
SET @pattern = '%' + REPLACE(REVERSE(LEFT(@pattern, LEN(@pattern) - 2)),')n(',')[0-9]%(')
SET @STR = REVERSE(@str)
RETURN LEFT(REVERSE(LEFT( @STR, PATINDEX(@pattern,@str)-1)), CHARINDEX(')',REVERSE(LEFT(@str, PATINDEX(@pattern,@str )-1)))-1)
END
GO
create table Test_FindIndexNumber
(AttributeKey varchar(100)null ,IndexKey varchar(100),
DisplayIndexNumber int null)
insert into Test_FindIndexNumber(AttributeKey,IndexKey,DisplayIndexNumber)
values ('A.B(1).C(1).D(10)','A.B(n).C(n)',null)
,('A.B(2).C(100).D(1000).F(333)','A.B(n).C(n)',null)
,('A.B(3).C(20).D(40).F(333).E(100)','A.B(n).C(n)',null)
,('A.B.C.D(10).F(100)','A.B.C.D(n)',null)
,('A.B.C.D(1000).K(333)','A.B.C.D(n)',null)
,('A.B.C.D(40).K(22).E(100)','A.B.C.D(n)',null)
SELECT dbo.f_OhMyGod(AttributeKey, IndexKey) As Result
FROM Test_FindIndexNumber
Please note, function may return error if pattern not found! You can add error handling as you wish.
I would still do it in CLR, if I would be you.
September 21, 2012 at 11:08 am
Thanks Eugene Elutin for your help.
Its working!!!!!!!!!!!!!!!!
Kumar.
September 21, 2012 at 12:57 pm
I'm glad to help, but make sure you understand how it's working. Otherwise you may have a trouble when someone ask you to explain it...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply