I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
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
Hi! Can anyone tell me how to perform a substring operation on a DateTime field. I'm using SQLServer. I have read that you may have to use casts but I have thus far been unable to get that to work. My statement: SELECT EventDate FROM tblEvent WHERE Category = 'GE'; I just want to cut the hours, minutes and seconds off, so that I am left with just the date element. Thanks all. Gren
According to the docs, when using substring on a text field (not varchar):
substring (<text>, start, length)
...the length in this case, represents BYTEs as opposed to number of characters. So my question is, how many bytes per character (or characters per byte)....or is this a possible conversion?
Hi :) and thanx for reading my post.I have to create a view based on a character in a number-field (fnr)which is 11 char's long. I have to get character nr "7".The problem is that i cannot use :--> SELECT fnr FROM table WHERE substring(fnr,7,1)since it's not a string.Any other way i can solve this ? Would be greatly and insanely happy ifanyone could help me out with a sample code or something.!Best regardsMirador*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Any help on extracting the time part from the datetime field in SQLdatabase.even though i had entered only the time part in the database when iextract the field it gives me only the date part. i’m using Vb.netdatagrid as a front end.any assistance appreciated!! :?:--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254266.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989
I have a Function where I want to put a character in a particular position in a Destination string. For example, I have a 5 character field containing, say, "12345". I would like to put the letter "A" in the third position resulting in "12A45". I'm trying the following
DECLARE @RetVal as varchar(5) SET SUBSTRING(@RetVal,3,1) = 'A'
I'm getting a compiler error
"incorrect syntax near '@RetVal'. Expecting '(' or SELECT"
Am I using the wrong Function to do this? If so, what should I be using?
I’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;; 20059958 ;2698;020225PU20;?;;;;;; 20059975Â Â Â Â ;;100777ST20;?;;;;;; 20059976 ;;;;;;;;; 11001980 ;;051168PU20;?;;;;;;1001980 20034832 ;;060253BO10;?;;;;;; 20055246 ;;1108731;?;?;;;;; 20043656Â Â ;;1022509;?;;;;;; 20059366 ;;1181750;31-12-2015;2;;;;; 20052712Â Â Â Â ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ;Â until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
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 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
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.
