extracting from a string

I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistanceTry:
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
I am trying to extract data within the first set of parenthesis in a string
that often times have multiple sets of data ..for example
(22)(223)(45) I just want the (22) or
(234)(12)...here just need the (234)
I see lots of text help on lengths but none that address a specified
character beginning and ending...
Thanks for any assistance|||Tom, here is my statement with you syntax...all I am getting is (22)
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select dbo.products_ourproducts_products.folderlist,
substring (@.str, charindex ('(', @.str), charindex (')', @.str))
from dbo.products_ourproducts_products
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Tom provided an example of the operation using a @.string variable. The
idea was that you would apply that expression to YOUR data. So, if
dbo.products_ourproducts_products.folderlist is the column from which
you want to extract the first item, it would be:
SELECT substring (X.folderlist,
charindex ('(', X.folderlist),
charindex (')', X.folderlist))
FROM dbo.products_ourproducts_products as X
All I did was replace @.str with your column reference. (And assign an
alias to make things more readable.)
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Tom, here is my statement with you syntax...all I am getting is (22)
>declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select dbo.products_ourproducts_products.folderlist,
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> from dbo.products_ourproducts_products
>
>"Tom Moreau" wrote:
>> Try:
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
>> I am trying to extract data within the first set of parenthesis in a string
>> that often times have multiple sets of data ..for example
>> (22)(223)(45) I just want the (22) or
>> (234)(12)...here just need the (234)
>> I see lots of text help on lengths but none that address a specified
>> character beginning and ending...
>> Thanks for any assistance
>>|||Tom, I might not be explaing very well, I have thousands of records and in
this field (folderlist)I have many different values...I only want the first
value in the field enclosed in parenthesis...some examples of the data are as
follows
(22)(223)(45) I want the (22)
(272)(24423) I want the (222)
(6) I want the (6)
(3422)(223)(45) I want the (3422)
Thanks
"Tom Moreau" wrote:
> Try:
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> I am trying to extract data within the first set of parenthesis in a string
> that often times have multiple sets of data ..for example
> (22)(223)(45) I just want the (22) or
> (234)(12)...here just need the (234)
> I see lots of text help on lengths but none that address a specified
> character beginning and ending...
> Thanks for any assistance
>
>|||Thank you, that worked fine.
"Roy Harvey (SQL Server MVP)" wrote:
> Tom provided an example of the operation using a @.string variable. The
> idea was that you would apply that expression to YOUR data. So, if
> dbo.products_ourproducts_products.folderlist is the column from which
> you want to extract the first item, it would be:
> SELECT substring (X.folderlist,
> charindex ('(', X.folderlist),
> charindex (')', X.folderlist))
> FROM dbo.products_ourproducts_products as X
> All I did was replace @.str with your column reference. (And assign an
> alias to make things more readable.)
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Tom, here is my statement with you syntax...all I am getting is (22)
> >
> >declare @.str varchar(100)
> > set @.str = '(22)(223)(45)'
> >
> > select dbo.products_ourproducts_products.folderlist,
> > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >
> > from dbo.products_ourproducts_products
> >
> >
> >"Tom Moreau" wrote:
> >
> >> Try:
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> >>
> >>
> >> --
> >> Tom
> >>
> >> ----
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> >> I am trying to extract data within the first set of parenthesis in a string
> >> that often times have multiple sets of data ..for example
> >>
> >> (22)(223)(45) I just want the (22) or
> >> (234)(12)...here just need the (234)
> >>
> >> I see lots of text help on lengths but none that address a specified
> >> character beginning and ending...
> >>
> >> Thanks for any assistance
> >>
> >>
> >>
>|||I have the value I need from the string, can I now remove the just
parenthesis that enclose it?
"Gerry M" wrote:
> Thank you, that worked fine.
>
> "Roy Harvey (SQL Server MVP)" wrote:
> > Tom provided an example of the operation using a @.string variable. The
> > idea was that you would apply that expression to YOUR data. So, if
> > dbo.products_ourproducts_products.folderlist is the column from which
> > you want to extract the first item, it would be:
> >
> > SELECT substring (X.folderlist,
> > charindex ('(', X.folderlist),
> > charindex (')', X.folderlist))
> > FROM dbo.products_ourproducts_products as X
> >
> > All I did was replace @.str with your column reference. (And assign an
> > alias to make things more readable.)
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> > On Wed, 30 Apr 2008 13:59:05 -0700, Gerry M
> > <GerryM@.discussions.microsoft.com> wrote:
> >
> > >Tom, here is my statement with you syntax...all I am getting is (22)
> > >
> > >declare @.str varchar(100)
> > > set @.str = '(22)(223)(45)'
> > >
> > > select dbo.products_ourproducts_products.folderlist,
> > > substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >
> > > from dbo.products_ourproducts_products
> > >
> > >
> > >"Tom Moreau" wrote:
> > >
> > >> Try:
> > >>
> > >> declare @.str varchar(100)
> > >> set @.str = '(22)(223)(45)'
> > >>
> > >> select
> > >> substring (@.str, charindex ('(', @.str), charindex (')', @.str))
> > >>
> > >>
> > >> --
> > >> Tom
> > >>
> > >> ----
> > >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > >> SQL Server MVP
> > >> Toronto, ON Canada
> > >> https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >>
> > >>
> > >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> > >> news:16A48C18-D464-4ADE-BADA-A245898703DF@.microsoft.com...
> > >> I am trying to extract data within the first set of parenthesis in a string
> > >> that often times have multiple sets of data ..for example
> > >>
> > >> (22)(223)(45) I just want the (22) or
> > >> (234)(12)...here just need the (234)
> > >>
> > >> I see lots of text help on lengths but none that address a specified
> > >> character beginning and ending...
> > >>
> > >> Thanks for any assistance
> > >>
> > >>
> > >>
> >|||On Thu, 1 May 2008 06:11:01 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>I have the value I need from the string, can I now remove the just
>parenthesis that enclose it?
Here is a version of what Tom originally posted, modified to leave off
the parentheses.
declare @.str varchar(100)
set @.str = '(22)(223)(45)'
select
substring(@.str, 2, charindex (')', @.str) -2)
I trust that you can apply this new expression in place of the old
version.
Roy Harvey
Beacon Falls, CT|||Roy, thanks for your help...I am getting a "Invalid length parameter passed
to the substring function." error with the following syntax..
update dbo.products_ourproducts_products set extratext02=substring(folderlist,2,charindex (')', folderlist) -2)
FROM dbo.products_ourproducts_products
Thanks again...
"Roy Harvey (SQL Server MVP)" wrote:
> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >I have the value I need from the string, can I now remove the just
> >parenthesis that enclose it?
> Here is a version of what Tom originally posted, modified to leave off
> the parentheses.
> declare @.str varchar(100)
> set @.str = '(22)(223)(45)'
> select
> substring(@.str, 2, charindex (')', @.str) -2)
> I trust that you can apply this new expression in place of the old
> version.
> Roy Harvey
> Beacon Falls, CT
>|||What do you get when you run the following:
SELECT charindex (')', folderlist),
folderlist
FROM dbo.products_ourproducts_products
WHERE charindex (')', folderlist) < 3
Roy Harvey
Beacon Falls, CT
On Thu, 1 May 2008 10:31:00 -0700, Gerry M
<GerryM@.discussions.microsoft.com> wrote:
>Roy, thanks for your help...I am getting a "Invalid length parameter passed
>to the substring function." error with the following syntax..
>update dbo.products_ourproducts_products set extratext02=>substring(folderlist,2,charindex (')', folderlist) -2)
>FROM dbo.products_ourproducts_products
>Thanks again...
>"Roy Harvey (SQL Server MVP)" wrote:
>> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
>> <GerryM@.discussions.microsoft.com> wrote:
>> >I have the value I need from the string, can I now remove the just
>> >parenthesis that enclose it?
>> Here is a version of what Tom originally posted, modified to leave off
>> the parentheses.
>> declare @.str varchar(100)
>> set @.str = '(22)(223)(45)'
>> select
>> substring(@.str, 2, charindex (')', @.str) -2)
>> I trust that you can apply this new expression in place of the old
>> version.
>> Roy Harvey
>> Beacon Falls, CT|||I get the following 7 records (of a selection of 2100)
(No column name) folderlist
0
0
0
0
0
0
0
"Roy Harvey (SQL Server MVP)" wrote:
> What do you get when you run the following:
> SELECT charindex (')', folderlist),
> folderlist
> FROM dbo.products_ourproducts_products
> WHERE charindex (')', folderlist) < 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 1 May 2008 10:31:00 -0700, Gerry M
> <GerryM@.discussions.microsoft.com> wrote:
> >Roy, thanks for your help...I am getting a "Invalid length parameter passed
> >to the substring function." error with the following syntax..
> >
> >update dbo.products_ourproducts_products set extratext02=> >substring(folderlist,2,charindex (')', folderlist) -2)
> >FROM dbo.products_ourproducts_products
> >
> >Thanks again...
> >
> >"Roy Harvey (SQL Server MVP)" wrote:
> >
> >> On Thu, 1 May 2008 06:11:01 -0700, Gerry M
> >> <GerryM@.discussions.microsoft.com> wrote:
> >>
> >> >I have the value I need from the string, can I now remove the just
> >> >parenthesis that enclose it?
> >>
> >> Here is a version of what Tom originally posted, modified to leave off
> >> the parentheses.
> >>
> >> declare @.str varchar(100)
> >> set @.str = '(22)(223)(45)'
> >>
> >> select
> >> substring(@.str, 2, charindex (')', @.str) -2)
> >>
> >> I trust that you can apply this new expression in place of the old
> >> version.
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>