Having a whale of a time, having tried almost every approach, except obviously the right one.
I have employee_no which is character size 10. It is right-justified for some reason. I'm using SUBSTRING to peel off the last five digits (it's going to a flat file where only 5 chars are allowed).
Now I just need to Left Justify the return string. What should I add
to this statement
substring(employees.employee_no,6,8)
My return is this:
A97 1Joe
A97 3Bruce
A97 4Scott
Many thanks in advance, you guys are amazing.
lisa
and this give's us the string part AFTER the , so that part is correct!
but I can't find out how to do the same thing but then for using everything BEFORE the ,
...
anyone got an idea? just changing Right with Left give's us (for example): http://thisisa instead of http://thisisatest.com (he's counting the number of characters from the right to the , and then displays the characters starting from the left but only the number of characters he counted previously) ...
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1 like in the following query : select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried: Â Â Â Â SELECT Â RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) Â Â Â Â Â FROM [Place] Â Â WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name FROM Users WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN ( SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users GROUP BY LEFT(name, CHARINDEX('_', name) - 1) HAVING (COUNT(*) > 1) )
does not work
is there any way to use a variable ?
declare @nm nvarchar set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
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?
