How To Match Records On Several Substring Functions
Dec 13, 2006
Hello All,
I have a sql statement that I'm using to match records on. But i need
to use a substring to get the nuber that I need, and I have to use
several substrings to get the number since its embedded in other text.
Here's my sql:
use fto_ups
select distinct o.Shipper#, o.InvoiceDate, o.InvoiceNumber, o.ChgType,
o.Lading, o.BillTo, o.Name2, o.Address1, o.Address2, o.City, o.State,
o.Zip,
o.ShipFromName1, o.ShipFromeName2, o.ShipFromAddress1,
o.ShipFromAddess2, o.ShipFromCity, o.ShipFromState, o.ShipFromZip,
o.PickupDate,o.PickUpRecord, o.TrackingNumber,
o.BOL, o.Reference2, o.ServiceType, o.ServiceZip, o.Zone, o.Weight,
o.PublishedCharge, o.IncentiveCredit, o.InsuredCharge, o.BilledCharge,
o.DelAreaSurcharge,
m.FRRDSSTrackingNbr,m.FRRDSSShipmentNbr, m.FRRDSSDivision,
m.FRRDSSDeptNbr, m.FRRDSSVendorNbr, m.FRRDSSChargeBackNbr,
m.FRRDSSShipmentCreateDate,m.[UPS Units],m.[UPS % Units to Total]
into dbo.tempOutBoundTable
from upsOutbound o left join msoutboundhistory m on right(o.bol,7) =
m.FRRDSSShipmentNbr and o.bol = m.FRRDSSShipmentNbr
********
How do I select the records using substring(o.bol,4,7) to add to the
above query? How do I inner join the records based on adding the
substring extracted o.bol?
I had 2 tables contain data: 1) old_data: 601,195 records firstname lastname address city state zip zip4
2) current_data: 410,185 records firstname lastname address city state zip zip4 3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.
****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.
/******* Delete current_data From old_data Cross Join current_data Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName and old_data.Address = current_data.Address ********/
I am using the following views from two db's to find records that don't match.
My question is can I have output of fields from the second table
SELECT distinct T1.[last name],t1.[first name],ENum FROM ECLINICIAN_Info T1 WHERE NOT EXISTS(SELECT * FROM ACLINICIAN_Info T2 WHERE t1.Enum = t2.Anum and t1.[last name] = t2.lname and t1.[first name] = t2.Fname)
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I have 2 tables GLSUMMARY and GLBUDGET, they are identical. I am joining them together with a left outer join from the SUMMARY to the BUDGET but when I dont have a matching BUDGET record on the join the SUMMARY gets dropped as well :eek:
Any help will be appreciated!
Here is the query! SELECT s.conu as CoNu, s.deptnu as DeptNu, s.fundnu as FundNu, s.acctnu as AcctNu, Sum(isNull(Amt01,0)) as Amt01, Sum(isNull(Amt02,0)) as Amt02, Sum(isNull(Amt03,0)) as Amt03, Sum(isNull(Amt04,0)) as Amt04, Sum(isNull(Amt05,0)) as Amt05, Sum(isNull(Amt06,0)) as Amt06, Sum(isNull(Amt07,0)) as Amt07, Sum(isNull(Amt08,0)) as Amt08, Sum(isNull(Amt09,0)) as Amt09, Sum(isNull(Amt10,0)) as Amt10, Sum(isNull(Amt11,0)) as Amt11, Sum(isNull(Amt12,0)) as Amt12, Sum(isNull(Amt13,0)) as Amt13, Sum(isNull(Bud01,0)) as Bud01, Sum(isNull(Bud02,0)) as Bud02, Sum(isNull(Bud03,0)) as Bud03, Sum(isNull(Bud04,0)) as Bud04, Sum(isNull(Bud05,0)) as Bud05, Sum(isNull(Bud06,0)) as Bud06, Sum(isNull(Bud07,0)) as Bud07, Sum(isNull(Bud08,0)) as Bud08, Sum(isNull(Bud09,0)) as Bud09, Sum(isNull(Bud10,0)) as Bud10, Sum(isNull(Bud11,0)) as Bud11, Sum(isNull(Bud12,0)) as Bud12, Sum(isNull(Bud13,0)) as Bud13 FROM shelbydb.shelby.GLSummary S left OUTER JOIN shelbydb.shelby.GLBudget B on (s.begindate = b.begindate) and (s.acctnu = b.acctnu) and (s.conu = b.conu) and (s.deptnu = b.deptnu) and (s.fundNu = b.fundNu) WHERE (s.begindate = '1/1/2004' and b.begindate = '1/1/2004') group by S.conu, S.deptnu, S.fundnu, S.acctnu, b.conu, b.deptnu, b.fundnu, b.acctnu
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied' AND [Number] ='A150731000039'
---- Removes incorrect records where these names match---- AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match) Last name (exact match) Address line 1 (exact match) Postal code/zip (exact match)
First Initial (exact match) Last name (exact match) DOB exact match Postal code/zip (exact match)
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
I was playing around with the new SQL 2005 CLR functionality andremembered this discussion that I had with Erland Sommarskog concerningperformance of scalar UDFs some time ago (See "Calling sp_oa* infunction" in this newsgroup). In that discussion, Erland made thefollowing comment about UDFs in SQL 2005:[color=blue][color=green]>>The good news is that in SQL 2005, Microsoft has addressed several of[/color][/color]these issues, and the cost of a UDF is not as severe there. In fact fora complex expression, a UDF in written a CLR language may be fasterthanthe corresponding expression using built-in T-SQL functions.<<I thought the I would put this to the test using some of the same SQLas before, but adding a simple scalar CLR UDF into the mix. The testinvolved querying a simple table with about 300,000 rows. Thescenarios are as follows:(A) Use a simple CASE function to calculate a column(B) Use a simple CASE function to calculate a column and as a criterionin the WHERE clause(C) Use a scalar UDF to calculate a column(D) Use a scalar UDF to calculate a column and as a criterion in theWHERE clause(E) Use a scalar CLR UDF to calculate a column(F) Use a scalar CLR UDF to calculate a column and as a criterion inthe WHERE clauseA sample of the results is as follows (time in milliseconds):(295310 row(s) affected)A: 1563(150003 row(s) affected)B: 906(295310 row(s) affected)C: 2703(150003 row(s) affected)D: 2533(295310 row(s) affected)E: 2060(150003 row(s) affected)F: 2190The scalar CLR UDF function was significantly faster than the classicscalar UDF, even for this very simple function. Perhaps a more complexfunction would have shown even a greater difference. Based on this, Imust conclude that Erland was right. Of course, it's still faster tostick with basic built-in functions like CASE.In another test, I decided to run some queries to compare built-inaggregates vs. a couple of simple CLR aggregates as follows:(G) Calculate averages by group using the built-in AVG aggregate(H) Calculate averages by group using a CLR aggregate that similatesthe built-in AVG aggregate(I) Calculate a "trimmed" average by group (average excluding highestand lowest values) using built-in aggregates(J) Calculate a "trimmed" average by group using a CLR aggregatespecially designed for this purposeA sample of the results is as follows (time in milliseconds):(59 row(s) affected)G: 313(59 row(s) affected)H: 890(59 row(s) affected)I: 216(59 row(s) affected)J: 846It seems that the CLR aggregates came with a significant performancepenalty over the built-in aggregates. Perhaps they would pay off if Iwere attempting a very complex type of aggregation. However, at thispoint I'm going to shy away from using these unless I can't find a wayto do the calculation with standard SQL.In a way, I'm happy that basic SQL still seems to be the fastest way toget things done. With the addition of the new CLR functionality, Isuspect that MS may be giving us developers enough rope to comfortablyhang ourselves if we're not careful.Bill E.Hollywood, FL------------------------------------------------------------------------- table TestAssignment, about 300,000 rowsCREATE TABLE [dbo].[TestAssignment]([TestAssignmentID] [int] NOT NULL,[ProductID] [int] NULL,[PercentPassed] [int] NULL,CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED([TestAssignmentID] ASC)--Scalar UDF in SQLCREATE FUNCTION [dbo].[fnIsEven](@intValue int)RETURNS bitASBEGINDeclare @bitReturnValue bitIf @intValue % 2 = 0Set @bitReturnValue=1ElseSet @bitReturnValue=0RETURN @bitReturnValueEND--Scalar CLR UDF/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]public static SqlBoolean IsEven(SqlInt32 value){if(value % 2 == 0){return true;}else{return false;}}};*/--Test #1--Scenario A - Query with calculated column--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignment--Scenario B - Query with calculated column as criterion--SELECT TestAssignmentID,CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END ASCalcColumnFROM TestAssignmentWHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1--Scenario C - Query using scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario D - Query using scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--Scenario E - Query using CLR scalar UDF--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignment--Scenario F - Query using CLR scalar UDF as crierion--SELECT TestAssignmentID,dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumnFROM TestAssignmentWHERE dbo.fnIsEven(TestAssignmentID)=1--CLR Aggregate functions/*using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct Avg{public void Init(){this.numValues = 0;this.totalValue = 0;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;}}public void Merge(Avg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;}}public SqlDouble Terminate(){if (numValues == 0){return SqlDouble.Null;}else{return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;}[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]public struct TrimmedAvg{public void Init(){this.numValues = 0;this.totalValue = 0;this.minValue = SqlDouble.MaxValue;this.maxValue = SqlDouble.MinValue;}public void Accumulate(SqlDouble Value){if (!Value.IsNull){this.numValues++;this.totalValue += Value;if (Value < this.minValue)this.minValue = Value;if (Value > this.maxValue)this.maxValue = Value;}}public void Merge(TrimmedAvg Group){if (Group.numValues > 0){this.numValues += Group.numValues;this.totalValue += Group.totalValue;if (Group.minValue < this.minValue)this.minValue = Group.minValue;if (Group.maxValue > this.maxValue)this.maxValue = Group.maxValue;}}public SqlDouble Terminate(){if (this.numValues < 3)return SqlDouble.Null;else{this.numValues -= 2;this.totalValue -= this.minValue;this.totalValue -= this.maxValue;return (this.totalValue / this.numValues);}}// private accumulatorsprivate int numValues;private SqlDouble totalValue;private SqlDouble minValue;private SqlDouble maxValue;}*/--Test #2--Scenario G - Average Query using built-in aggregate--SELECT ProductID, Avg(Cast(PercentPassed AS float))FROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario H - Average Query using CLR aggregate--SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS AverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID--Scenario I - Trimmed Average Query using built in aggregates/setoperations--SELECT A.ProductID,CaseWhen B.CountValues<3 Then NullElse Cast(A.Total-B.MaxValue-B.MinValue ASfloat)/Cast(B.CountValues-2 As float)End AS AverageFROM(SELECT ProductID, Sum(PercentPassed) AS TotalFROM TestAssignmentGROUP BY ProductID) ALEFT JOIN(SELECT ProductID,Max(PercentPassed) AS MaxValue,Min(PercentPassed) AS MinValue,Count(*) AS CountValuesFROM TestAssignmentWHERE PercentPassed Is Not NullGROUP BY ProductID) BON A.ProductID=B.ProductIDORDER BY A.ProductID--Scenario J - Trimmed Average Query using CLR aggregate--SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) ASAverageFROM TestAssignmentGROUP BY ProductIDORDER BY ProductID
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
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.
