"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 ")"
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.
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
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.
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.
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 @
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
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.
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)'.
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?
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
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.
Thanks for responding. The problem is still persistent. What I want to do is to display two names in a combobox and the box should only be as wide as 2 adjacent names for their actual length. Whether I use char or varchar the combobox is always twice the width defined for the fields. So we will try to find a solution: an elegant one rather than a workaround. Any new ideas are still welcome.
I have an address field that has data that needs to parsed. The data has different formats. I have tried charindex and substring but I am not getting the results I need.
Here is what the data looks like:
Paper Mill Rd 3332 Allegheny Ave 13 1/2 Balto Nat'l Pike 6600 R-2 Rolling Rd N 3030
Here is the results I am looking for:
street dir street number street name street type street unit 3332 Paper Mill Rd 13 1/2 Allegheny Ave 6600 Balto Nat'l Pike R-2 N 3030 Rolling Rd
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30)) insert into @t select '2' union all select '[5] [4]' union all select ' [12] [1]'
so i need to get...
col ------- 2 5 12
EDIT- OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30)) insert into @t --select '2'union all select '[5] [4]'union all select ' [12] [1]'
select col ,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) from @t
We capture the firstname of a person along with their middle intial in our contacts table . How do i use the substring function to get just the firstname and omit the middle initial
Sample Data
Jennifer K Jason R Samuel V
I want just the firstname without the space and middle initial
insert into @bid (streetName) select '12 union street' union all select '1A apple avenue' union all select '11A apple st avenue road' union all select 'POB 23'
select * from @bid
update @bid set streetNumber = case when substring(streetName,1,charindex(' ',streetName)) like '[1-9]' OR substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9]' OR substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9]' then substring(streetName,1,charindex(' ',streetName)) else null end,
streetNameSuffix = case when substring(streetName,1,charindex(' ',streetName)) like '[1-9][a-z]' OR substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][a-z]' OR substring(streetName,1,charindex(' ',streetName)) like '[1-9][1-9][1-9][a-z]' then substring(streetName, 1, charindex(' ', streetName)) else null end,
I need help capturing information from a free text field.The 10 examples below contain examples of the information I am tryingto capture.In each cell I am trying to capture the number between 'TranID=' andthe next '&'.So in the fisrt cell I would like to capture 14078800.Things you will need to keep in mind are;The number is not of fixed length.'TranID' will always precede the numberThe number will always be followed by an '&'The '&' sign can occur multiple times in the text.Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivCode=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=delivRegards,Ciarán