November 21, 2012 at 4:01 am
Hi,
Below given is my requirement.
declare @SPos int,@EPos int
set @SPos = CHARINDEX('[','{ ActionOption : [ Past Audit Window ] }')+1
select @SPos
set @EPos = CHARINDEX(']','{ ActionOption : [ Past Audit Window ] }')-2
select @EPos
select
SUBSTRING('{ ActionOption : [ Past Audit Window ] }',@SPos,@EPos)
Here I want to display only the data given with [ and ]. e.g. Past Audit window.
Just for Sample here it is Past Audit window. But I can have anything within [ ], like [QA],[Cancel]. But I want to select only the data within [ and ].
Any help?
Regards
Kalyani
November 21, 2012 at 4:12 am
select LTRIM(RTRIM(
REPLACE(
REPLACE('{ ActionOption : [ Past Audit Window ] }','{ ActionOption : [','')
,'] }','')))
November 21, 2012 at 4:26 am
Thank you, It works fine for whatever the sample I have given.
But, I also have a sample like this
{ ActionOption : [ Selection Error ] },{ SubstatusName : [ Cancelled and Locked ] }.
{ ActionOption : [ Record Fees Too High ] },{ SubstatusName : [ Cancelled ] }
But I want only the data within [ and ] where I have ActionOption.
E.g., Here I want only Selection Error and Record Fees Too High to be displayed
November 21, 2012 at 4:30 am
Kalyani,
The 3rd parameter for substring is not End position . but its length. Just changed a bit your statements.
declare @SPos int,@EPos int,@Len int
set @SPos = CHARINDEX('[','{ ActionOption : [ Past Audit Window ] }')+1
select @SPos
set @EPos = CHARINDEX(']','{ ActionOption : [ Past Audit Window ] }')-2
select @EPos
set @len=@EPos - @SPos + 1
select
SUBSTRING('{ ActionOption : [ Past Audit Window ] }',@SPos,@Len)
November 21, 2012 at 4:36 am
Thank you. It is sensible in taking the length for the example I have given. But I also have another few samples, which I gave in earlier thread. It will not work for the other samples
November 21, 2012 at 5:26 am
Hi,
I got the code for this, from one of my friend and is working fine for all the samples.Here is the code.
SELECT
LEFT(
REPLACE(('{ ActionOption : [ Past Audit Window ] }'),
'{ ActionOption : [ ',
''
),
CHARINDEX
(
' ]',
REPLACE(('{ ActionOption : [ Past Audit Window ] }'),
'{ ActionOption : [ ',
''
),
1
)
)
November 21, 2012 at 5:35 am
kalyav21 (11/21/2012)
Thank you, It works fine for whatever the sample I have given.But, I also have a sample like this
{ ActionOption : [ Selection Error ] },{ SubstatusName : [ Cancelled and Locked ] }.
{ ActionOption : [ Record Fees Too High ] },{ SubstatusName : [ Cancelled ] }
But I want only the data within [ and ] where I have ActionOption.
E.g., Here I want only Selection Error and Record Fees Too High to be displayed
I'm glad that you found the way to do what you need.
Actually, to get relevant help on forum, you need to supply as much details as possible, as we cannot see what you can see on your PC and we would have no idea about all possible cases you might have.
Please read the article from the link at the bottom of my signature, so the next time it will help you to get more relevant help on this forum.
Good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply