Extracting Data

On Sep 9, 3:46 pm, SetonSoftware wrote:
> On Sep 9, 2:51 pm, Kru wrote:
>
>
>
> > Hi.
>
> > I need some help on extracting certain data from a field.
>
> > For example I want to extract anything after the CVC-IBM or CVC-
> > Covance
>
> > CVC-IBM HARROGATE
> > CVC-COVANCE EMEA
>
> > So the column field would be
> > HARROGATE
> > EMEA
>
> > Can this be done?
>
> > Any help is appreciated.
>
> > Thanks.
>
> You mean something like this:
>
> SELECT SUBSTRING('CVC-IBM HARROGATE', CHARINDEX('CVC-IBM HARROGATE',
> 'CVC-IBM') + LEN('CVC-IBM') + 2, 100)
> SELECT SUBSTRING('CVC-COVANCE EMEA', CHARINDEX('CVC-COVANCE EMEA',
> 'CVC-COVANCE') + LEN('CVC-COVANCE') + 2, 100)
>
> Thanks
>
> Carl

Well there are a lot in the database with different locations. Is
there an easier than this? Below is just a glimpse but the column goes
on and on.

CVC-IBM SYDNEY
CVC-IBM SINGAPORE
CVC-IBM PRINCETON
CVC-IBM HARROGATE
CVC-COVANCE EMEA
CVC-IBM PRINCETON
CVC-IBM SHANGHAI
CVC-IBM SHANGHAI
CVC-IBM SHANGHAI
CVC-COVANCE EMEA
CVC-IBM MAIDENHEAD
CVC-COVANCE EMEA
CVC-COVANCE EMEA
 >> Stay informed about: Extracting Data 

On Sep 9, 4:46 pm, "Tom Cooper" wrote:
> Select Case When YourColumnName Like 'CVC-IBM %' Then
> SubString(YourColumnName, 9, Len(YourColumnName))
>   When YourColumnName Like 'CVC-COVANCE %' Then SubString(YourColumnName,
> 13, Len(YourColumnName))
>   Else YourColumnName End
> From YourTableName
>
> Tom
>
> "Kru" wrote in message
>
>
> On Sep 9, 3:46 pm, SetonSoftware wrote:
>
>
>
> > On Sep 9, 2:51 pm, Kru wrote:
>
> > > Hi.
>
> > > I need some help on extracting certain data from a field.
>
> > > For example I want to extract anything after the CVC-IBM or CVC-
> > > Covance
>
> > > CVC-IBM HARROGATE
> > > CVC-COVANCE EMEA
>
> > > So the column field would be
> > > HARROGATE
> > > EMEA
>
> > > Can this be done?
>
> > > Any help is appreciated.
>
> > > Thanks.
>
> > You mean something like this:
>
> > SELECT SUBSTRING('CVC-IBM HARROGATE', CHARINDEX('CVC-IBM HARROGATE',
> > 'CVC-IBM') + LEN('CVC-IBM') + 2, 100)
> > SELECT SUBSTRING('CVC-COVANCE EMEA', CHARINDEX('CVC-COVANCE EMEA',
> > 'CVC-COVANCE') + LEN('CVC-COVANCE') + 2, 100)
>
> > Thanks
>
> > Carl
>
> Well there are a lot in the database with different locations. Is
> there an easier than this? Below is just a glimpse but the column goes
> on and on.
>
> CVC-IBM SYDNEY
> CVC-IBM SINGAPORE
> CVC-IBM PRINCETON
> CVC-IBM HARROGATE
> CVC-COVANCE EMEA
> CVC-IBM PRINCETON
> CVC-IBM SHANGHAI
> CVC-IBM SHANGHAI
> CVC-IBM SHANGHAI
> CVC-COVANCE EMEA
> CVC-IBM MAIDENHEAD
> CVC-COVANCE EMEA
> CVC-COVANCE EMEA

Thanks Tom! Would it make difference if the database is a db2? I am
getting errors that it doesn't like the Len function.
 >> Stay informed about: Extracting Data 

Select Case When YourColumnName Like 'CVC-IBM %' Then
SubString(YourColumnName, 9, Len(YourColumnName))
When YourColumnName Like 'CVC-COVANCE %' Then SubString(YourColumnName,
13, Len(YourColumnName))
Else YourColumnName End
From YourTableName

Tom

"Kru" wrote in message

On Sep 9, 3:46 pm, SetonSoftware wrote:
> On Sep 9, 2:51 pm, Kru wrote:
>
>
>
> > Hi.
>
> > I need some help on extracting certain data from a field.
>
> > For example I want to extract anything after the CVC-IBM or CVC-
> > Covance
>
> > CVC-IBM HARROGATE
> > CVC-COVANCE EMEA
>
> > So the column field would be
> > HARROGATE
> > EMEA
>
> > Can this be done?
>
> > Any help is appreciated.
>
> > Thanks.
>
> You mean something like this:
>
> SELECT SUBSTRING('CVC-IBM HARROGATE', CHARINDEX('CVC-IBM HARROGATE',
> 'CVC-IBM') + LEN('CVC-IBM') + 2, 100)
> SELECT SUBSTRING('CVC-COVANCE EMEA', CHARINDEX('CVC-COVANCE EMEA',
> 'CVC-COVANCE') + LEN('CVC-COVANCE') + 2, 100)
>
> Thanks
>
> Carl

Well there are a lot in the database with different locations. Is
there an easier than this? Below is just a glimpse but the column goes
on and on.

CVC-IBM SYDNEY
CVC-IBM SINGAPORE
CVC-IBM PRINCETON
CVC-IBM HARROGATE
CVC-COVANCE EMEA
CVC-IBM PRINCETON
CVC-IBM SHANGHAI
CVC-IBM SHANGHAI
CVC-IBM SHANGHAI
CVC-COVANCE EMEA
CVC-IBM MAIDENHEAD
CVC-COVANCE EMEA
CVC-COVANCE EMEA
 >> Stay informed about: Extracting Data 

I think the equivalent DB2 function is LENGTH() - but I don't work with DB2
and could well be wrong. But this is a SQL Server group, you are much
better off asking DB2 questions in a DB2 forum. And if you must ask a DB2
question here - for example if you are trying to access a SQL Server
database from a DB2 database or vice versa - make sure you identify the fact
that you are using a DB2 database.

Tom

"Kru" wrote in message

On Sep 9, 4:46 pm, "Tom Cooper" wrote:
> Select Case When YourColumnName Like 'CVC-IBM %' Then
> SubString(YourColumnName, 9, Len(YourColumnName))
> When YourColumnName Like 'CVC-COVANCE %' Then SubString(YourColumnName,
> 13, Len(YourColumnName))
> Else YourColumnName End
> From YourTableName
>
> Tom
>
> "Kru" wrote in message
>
>
> On Sep 9, 3:46 pm, SetonSoftware wrote:
>
>
>
> > On Sep 9, 2:51 pm, Kru wrote:
>
> > > Hi.
>
> > > I need some help on extracting certain data from a field.
>
> > > For example I want to extract anything after the CVC-IBM or CVC-
> > > Covance
>
> > > CVC-IBM HARROGATE
> > > CVC-COVANCE EMEA
>
> > > So the column field would be
> > > HARROGATE
> > > EMEA
>
> > > Can this be done?
>
> > > Any help is appreciated.
>
> > > Thanks.
>
> > You mean something like this:
>
> > SELECT SUBSTRING('CVC-IBM HARROGATE', CHARINDEX('CVC-IBM HARROGATE',
> > 'CVC-IBM') + LEN('CVC-IBM') + 2, 100)
> > SELECT SUBSTRING('CVC-COVANCE EMEA', CHARINDEX('CVC-COVANCE EMEA',
> > 'CVC-COVANCE') + LEN('CVC-COVANCE') + 2, 100)
>
> > Thanks
>
> > Carl
>
> Well there are a lot in the database with different locations. Is
> there an easier than this? Below is just a glimpse but the column goes
> on and on.
>
> CVC-IBM SYDNEY
> CVC-IBM SINGAPORE
> CVC-IBM PRINCETON
> CVC-IBM HARROGATE
> CVC-COVANCE EMEA
> CVC-IBM PRINCETON
> CVC-IBM SHANGHAI
> CVC-IBM SHANGHAI
> CVC-IBM SHANGHAI
> CVC-COVANCE EMEA
> CVC-IBM MAIDENHEAD
> CVC-COVANCE EMEA
> CVC-COVANCE EMEA

Thanks Tom! Would it make difference if the database is a db2? I am
getting errors that it doesn't like the Len function.
 >> Stay informed about: Extracting Data