Extract Substring In Statement

Feb 12, 2015

I tried to extract the information in a field

Samples of the data:

"Current Date updated ( 12/31/2015 -> 12/31/2016 )"
"Status updated ( Open -> Should be Closed )"
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"

These are what all records look like:

1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"

View 1 Replies


ADVERTISEMENT

DB Design :: Two Columns - How To Extract Substring

Apr 23, 2015

I have two columns "Project Name" and "Flag". 

Now i want to find out all the project names which contain a particular substring in it, like say - "SRM" and update the "Flag" column to value 1 for all those projects.

View 15 Replies View Related

Extract Substring From String(Regular Expressions)

Feb 11, 2008

Hello forum, I need extract a substring form a string that follows a regular expressions -->

T:1º/PQ:1/TALLA:2(MOD.51 100/150)

T:<number>º/PQ:<number>...

I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...

I would like me use it as:
SELECT RegularExpression (Field)

Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String

Jul 9, 2014

I have a column that contains the follwoing string I need to compare.

ek/df/cv/
ek/df/cv/f

All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.

I can't do it by using the combination of Substring() and charindex() and Len()

View 9 Replies View Related

String Index Function (substring / Charindex) - Extract Specific Characters From Data

Aug 5, 2013

SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

View 4 Replies View Related

Substring Sql Statement

Aug 31, 2007

I have a SQL column that contains something like this:






ORLANDO        ,FL.    32803

COCOA       , FL. 32922-8617

SATELLITE BCH,FL  32937-3523

TAMPA       , FL. 33609-3105

EAU GALLIE      ,FL.   32935
I need only the name of the city, dont need FL or the zip code, how can I do a substring statement on sql to get only that?
Thanks,
Erick

View 8 Replies View Related

CASE Statement With Substring

Feb 13, 2008



I have a field in our database that I need to update. The data is stored as follows:
26-1000-1
126-1200-22
125-1400-22

I want to make it
PRHS-1000-1
PRHH-1200-22

This is what I have been working with-

select te.districtirn
, te.fiscalyear
, te.period
, te.emiscourse
,case

when substring(te.emiscourse,1,2) = '26' then 'PRHS'

end
from tblemscls te

Thanks,
Nick

View 12 Replies View Related

Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?

Apr 6, 2007

I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.









Table 1

Org

aaa:aaa:aa

aaa:aaa:ab

aaa:aab:aa








Table 2














delim

Seg1Start

Seg1Len

Seg2Start

Seg2Len

Seg3Start

Seg3Len


:

1

3

5

3

9

2



My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.



Any ideas? Can this be done in SSIS?



I'd really appreciate any insight that anyone might have.



Regards,

Bill

View 23 Replies View Related

Extract SQL Select Statement

Nov 29, 2006

Hi,

View 1 Replies View Related

Extract Parameters From SQL Statement

Jul 20, 2005

Hei,I'm currently trying to write a program in C# that will allow users toparametrize their queries.For instance, I have a query like this:SELECT * FROM Customers Where Region = @Region AND Gender > @GenderHow can I extract the Parameters names without using Stringmanipulation (which is not perfect since sql statements can alsocontain '@' in LIKE clauses for example)I tried this wayDim comm As New OleDbCommand(SqlStatement, myCon)comm.Prepare()MsgBox(comm.Parameters.Count.ToString())comm.Dispose()myCon.Close()but this always return me 0 (zero)Do you have any idea on how to solve this generic problem?RegarsPhilippe Graca

View 3 Replies View Related

Extract Data In Insert Into... Statement Format

Jul 23, 2005

Is there a way in SQL Server 2000 to extract data from a table, such thatthe result is a text file in the format of "Insert Into..." statements, i.e.if the table has 5 rows, the result would be 5 lines of :insert into Table ([field1], [field2], .... VALUES a,b,c)insert into Table ([field1], [field2], .... VALUES d, e, f)insert into Table ([field1], [field2], .... VALUES g, h, i)insert into Table ([field1], [field2], .... VALUES j, k, l)insert into Table ([field1], [field2], .... VALUES m, n, o)Thanks in advance

View 5 Replies View Related

Need Sql Script Or Statement To Extract All Relations From One Tree

Oct 7, 2006

Suppose I have a table with relations (REL). In this table there are relations between parent en child, so the columns are: Rel_Id, parent_Id, child_Id. Example:

1, A, B
2, A, C
3, A, D
4, B, E
5, C, F
6, G, H
7, G, I
8, H, J

I need a query which returns all relations from REL that are in the same tree as the input unit.
In the example, giving D as input unit, it should return relations 1, 2, 3, 4 and 5, because A, B, C, E and F are (in)directly related to D and belong therefore to the same tree.
Giving H as input, it should return relations 6, 7 and 8.
(with a tree, I mean that a parent can have 0, 1 or more children and a child belongs at most one parent.)

Thanks for your help in advance!

View 11 Replies View Related

Substring

Jul 5, 2001

Hi all,
I have just started using SQL7 and quite dumb at it.
Here is my problem

i have tables ip_address and ip_subnets. both contains more than 20,000 records. Though ideally each subnet should correspond to only one ip address it is not so due to SMS inventory and remote clients configurations etc.

As an example If my ip address is 141.151.128.78 I need to select only 141.151.128.64 ( or atleast 141.151.128.*)as the valid subnet. In other words I need to compare upto 3rd octet and only if it matches with ip address then declare that as the valid subnet.
Pls note that ip addresses vary for each machine though there will 4 octets, I can't use character positions as the nos in each octet might vary from 1-255.

Any help would be greatly appreciated
Pasted here under is the query script I am playing around with charcter poistions which are not working in my favor. Just added to explain my problems in more clearer way
select distinct system_data.name0, System_IP_Address_ARR.ip_addresses0, System_IP_subnets_Arr.ip_subnets0, system_disc.client0 from system_Data, System_IP_Address_ARR, system_ip_subnets_arr, system_disc where system_data.machineid = System_IP_Address_ARR.itemkey and system_data.machineid = system_ip_subnets_arr.itemkey and system_data.machineid = system_disc.itemkey and system_disc.client0 = 1 and substring(System_IP_Address_ARR.ip_addresses0,1,10 ) = substring(System_IP_subnets_ARR.ip_subnets0,1,10) and substring(System_IP_Address_ARR.ip_addresses0,10,1 ) = '.' and System_IP_subnets_ARR.ip_subnets0 not in ('11%.%.%.%','12%.%.%.%', '10%.%.%.%' , '10.%.%.%' , '1.%.%.%') order by System_DATA.name0


Arun

View 2 Replies View Related

Need Help On Substring

Nov 5, 2001

Hey you sql programming guru's

I need help seperating a name from first name and last name

The field is like this,

last name, first name
example Doe, John
I need to seperate the last name from the , to the first character
and the last name from the , to the last character.

I think I have to use a substring but not sure how tell it to stop and
start when it gets to the comma.

Can someone please help me.

Thanks,
Dianne

View 1 Replies View Related

SubString

Aug 8, 2001

Need to do something like SUBSSTRING in Access, any ideas?

View 1 Replies View Related

Using Substring

Oct 4, 2004

Hi

Can anyone show me how to do the following:

I have this snippet of data

ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270

I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.

So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270


Thanks in advance

View 9 Replies View Related

Substring Help

Feb 4, 2005

Hi,
please help me in developing query to satisfy this...
i have table called test and the table data looks like this.

col1
123.abc
1.ab
12.cba

the query needs to return all rows data after . the result set should return this

abc
ab
cba

appreciate your help..
thanks
sskris

View 1 Replies View Related

Substring

Feb 3, 2004

I have a field that contaings 15 characters. I want to just pull the first 6. So anything that matches these 6 will be returned.

View 10 Replies View Related

Substring???

Apr 8, 2004

We have entries like below in a table. I need a query by which I can get the CustomerName from the below entries.


ACustomerName
ACustomerNameCredit
ACustomerNameDebit

Thanks!

View 14 Replies View Related

Substring

Jun 2, 2004

I an trying gto devide this one field that contains city state and zip into 3 seperate columns. The Column right now looks like this:

Coulumn1
-------------------------------
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210


I tryed this:

use cimpro1
select substring(cust_shipto_addr_l3, 1, 19) as 'City',
substring(cust_shipto_addr_l3, 20, 21) as 'State',
substring (cust_shipto_addr_l3, 22, 31) as 'Zip'
from opcshto


For some reason, when I run the query I get this for State:

State
-------------------------
CA91733617
LA71172
CA90249107
WI53216
IL61944
IL62832
MN56283
ME04210


When I use the substring to only pull characters 20 and 21 it pulls everything startign at 20. I just want it to select character position 20 ans 21 for the state. As far as the substring for City and Zip, everything comes out fine. Its just State that I am having trouble with.

Any help is appreciated.

View 7 Replies View Related

Substring

Sep 13, 2006

iam trying to write a string function which will give me the id part of a mail id
but iam geting the string along with @ and when iam trying to remove the last char (@) iam getting error

query:
select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail)))
from lead_details -----> Gives me id along with @


select substring(leadassignedtombemail,1,(CHARINDEX('@', leadassignedtombemail) - 1))
from lead_details ------------>gives me error "Invalid length parameter passed to the substring function."

But
select (CHARINDEX('@', leadassignedtombemail) - 1) from lead_details
works and gives me the length of id without counting @

where did i go wrong

View 2 Replies View Related

Help With Substring

Dec 1, 2006

i am trying to get the last name of the customer, but my db has the names stored as (first,middle, last) order in a single field. i am using the statment:
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST

but it only works if the customer does not have a middle name, otherwise it returs the middle+last as the last name. what should i do/ any ideas??? here is my code

select ActPrjMgr
,ActEmpId
,ActEmpName
,ActCustName
,RIGHT(ActCustName,LEN(ActCustName) - CHARINDEX(' ',ActCustName) ) AS LAST
,ActPrjCode
,left(ActPrjType,2) as Status
,ActEmpTaskCode
,left(ActBillingPeriod,11)as ppedate
,left(ActivityDate,11) as actdate
,ActTimevalue
from dbo.ACTIVITIES
where ActBudCat = 'labor'
and ActBillingPeriod = '11/17/2006'
and actprjcode <> ' '
and actprjcode is not null
--and ActBillingPeriod = @StartDate
order by ActPrjMgr
,ActEmpID
,ActEmpTaskCode
,ActivityDate

View 7 Replies View Related

Substring

Mar 19, 2007

Hi All

Iam new to sql, Iam using sql 2000 and have a field datatype varchar.

An example of the string: -

CN=Leighton Morgan/OU=WR/O=Extranet

I need to write a query that will allow me to extract the following:-

Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Please can you help

Many Thanks

View 9 Replies View Related

Substring

Mar 19, 2007

Hi All

Iam new to sql, Iam using sql 2000 and have a field datatype varchar.

An example of the string: -

CN=Leighton Morgan/OU=WR/O=Extranet

I need to write a query that will allow me to extract the following:-

Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Please can you help

Many Thanks

View 1 Replies View Related

Substring

Mar 19, 2007

Hi All

Iam new to sql, Iam using sql 2000 and have a field datatype varchar.

An example of the string: -

CN=Leighton Morgan/OU=WR/O=Extranet

I need to write a query that will allow me to extract the following:-

Name: Leighton Morgan
Company: WR
and lastly the word Extranet

Please can you help

Many Thanks

View 1 Replies View Related

Substring

Jan 25, 2006

i have a column that has "Full name" of an individual.

I want to create a new column with just the first name.

How do i populate this column with just the first name...ie it just takes the string until the space from the "full name" column.

View 5 Replies View Related

Substring

Oct 22, 2007

can anyone help me with the following report.

Create procedure Rpt_StaffDevelopmentReport
(@cmb1 as varchar(100),
@cmb2 as varchar(100) with encryption as

Begin
--@cmb1 is the site name (s.sitename), @cmb2 is the staffname in the form €˜surname,forename€™

--@cmb2 will need to be split based on the €˜,€™ within the string
--tables needed are staffDevelopment , staffMember and Site
end


Three Tables.

Site
Columns
Siteid, sitename, address1, address2

Staffmember
Staffed, surname, forename,sex, address1, address2, siteid

Staffdevelopment
Staffed, development, personal

View 7 Replies View Related

Substring Problem

Feb 1, 2007

Hi
 
In  my status table I m having field like
Actionby (COLUMN NAME)
-------------------
TravelDept
TravelDept
Approver
FinanceDept
TravelDept(xyz@yahoo.com)
 
I m having mail id along with TravelDept in some rows.
I want to retreive rows containing TravelDept.When i am retreving i wnat to retreive value 'TravelDept' which is having mail id also.
i have written one query,but its retreving only the value which is having mail id.
i want to retreive TravelDept value with mail id and Only TravelDept values also.
my query is:
select replace(SUBSTRING(S.Actionby,CHARINDEX('',S.Actionby,1) , CHARINDEX('(',S.Actionby,1)+0 ),')', '')  from status S;
But this query retreiving only 1 row only insteadof 3 rows.
Total 3  rows having TravelDept value.I want to retreive all these 3 rows.
please help me.
 
Thanks.
 
 

View 2 Replies View Related

Can You Use Substring In ASP.NET On A DropDownList ?

Oct 26, 2007

I'm using the GridView to display some accounting infromation. I have a project where I have a 14 character control number. I would like to have a dropdown list to select the account classification of records to be displayed. The accounting classification is the first two characters of the control number.  So the dropdown list needs to show unique first two characters and the GridView will be filtered on these two characters.  I have been trying to use "substring" in the ASP.NET code; not even sure you can.
Any suggestions on how to accomplish this would be greatly appreciated.  See code below:
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="control_num" DataValueField="control_num">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:GPCRReportsConnectionString %>"
SelectCommand="SELECT DISTINCT [substring(control_num,1,2)] FROM [Request]"></asp:SqlDataSource>
ERROR: Invalid column name 'substring(control_num,1,2)'.

View 6 Replies View Related

T-SQL SUBSTRING For RequestDateTime

Dec 11, 2007

I tried to mimic some code I found but on RequestDateTimeSUBSTRING(RequestDateTime,(charindex(':',RequestDateTime)+1),len(RequestDateTime)) AS 'Date Downloaded'I can only use SUBSTRING on char type strings.How would I get maybe only up to the first : character in the RequestDateTime? 

View 2 Replies View Related

Substring(very Very Urgent)

May 6, 2008

 hi frenz:
Can any one send me the code for
substring multiple cell values of same column and put it into one cell of that column.

View 3 Replies View Related

Updating A Substring

May 29, 2008

 Hi I am trying to update a part of a date string using the following sql statement. Alas, I'm having no joy.cmd.CommandText = "UPDATE tb_bookings SET SUBSTRING(startDate,12,5) = @newStartTime  WHERE requestPackID = @reqPackID";   The date has been saved  in the db in the following format:Fri May 23 17:30:00 GMT+0900 2008 Any idea why this isn't working. Any help much appreciated.Barry 

View 4 Replies View Related

SQL Substring Syntax

Jan 28, 2006

Hi,
I'm trying to do write a query in SQL that returns the last two characters in a field.
If the actual fields are sheep, dog, cat, parrot
I want the query to return ep, og, at, ot
After rooting around I've discovered the SQL 'substring' function.
The problem I have is that the field has a variable length, so I need a way of passing the strign length to substring.
Its probably simple to solve, but not for a rookie like me without an example and I am (as always) grateful for any ideas. 

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved