Transact SQL :: String Function To Extract Matching Rows

Oct 6, 2015

Consider the following: I have a table, say ORDERS, with these entries -

CustID
ProductID
1       CAN
2       2
3       1,2
4       4
5       1,2,3,4,5,CAN
6       10
7       CAN
8       1,CAN

I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.

View 12 Replies


ADVERTISEMENT

Transact SQL :: Get Rows Not Matching Prior Year

Apr 25, 2015

We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd].  If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result.  Below is how they designed the table and below that is the stored procedure to pull the records. 

CREATE TABLE [dbo].[BillingHistory](
[BillingHistoryID] [int] IDENTITY(1,1) NOT NULL,
[entity] [varchar](4) NULL,
[plincd] [varchar](3) NULL,
[pgrpcd] [varchar](4) NULL,
[pitmcd] [varchar](4) NULL,
[newplincd] [varchar](2) NULL,

[Code] ....

View 13 Replies View Related

Transact SQL :: Extract String With Delimiter

Nov 2, 2015

I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.

View 4 Replies View Related

SQL Server 2012 :: Selecting Matching Rows Which Exist In Particular String?

Apr 3, 2015

Suppose I have string like

@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'

and table contains two columns having rows like,

ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx

how can I search a ID's which are exist in my string.

result should be,

3 abcd Oh
6 abcd O

View 4 Replies View Related

SQL Server 2012 :: Select Matching Rows Which Exist In Particular String?

Apr 14, 2015

Suppose I have string like

DECLARE @strname varchar(50) = 'i_ncm_ai04';
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,SD_TEXT VARCHAR(10) NOT NULL

[Code] ......

how can I search a ID's which are exist in my string.

result should be,

3 i_ncm
8 i_ncm_a

View 2 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names  for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT  A.CID, 
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

Transact SQL :: Extract ID And Specific Part Of A String From Column

Jun 10, 2015

I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.

I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)

The row sample looks like the following

I have to extract the Id and a part of the column from mystring.

Id      MyString
1      ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|

[Code] ....

The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following

Id      DesiredString
1      Extracts^This^String1
2      Extracts^This^String2
3      Extracts^This^String3

Is there a way to extract this either using TSQL or SSIS.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,

[Code] ....

View 4 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

SQL Server 2008 :: Pattern Matching And Extract Text

Sep 17, 2015

I have two tables in SQL Server--an expression table and a translate table.

DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)

INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'

[Code] ....

I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.

So,

CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67

View 8 Replies View Related

Transact SQL :: Function To Tokenize A String Of Characters Greater Than 4000

Jul 21, 2015

I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work.  

View 2 Replies View Related

Transact SQL :: String Manipulation Single To Multiple Rows

Nov 7, 2015

The recipe preparation instructions are stored in a table by RecipeID.  The prep instructions are in a single VARCHAR(MAX) column and look something like this:  

1.  Boil Water 
2.  Add noodles 
3.  Add cheese sauce 
4.  Stir well

Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row.  How would I accomplish this in T-SQL?

View 11 Replies View Related

T-SQL (SS2K8) :: Unable To Extract Data Based On Matching Info From A Local Database

Oct 2, 2014

I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,

I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..

Is there a simple tweak that would speed it up?

USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT

[Code]....

View 4 Replies View Related

Transact SQL :: Invalid Length Parameter Passed To LEFT Or SUB-STRING Function

Jul 22, 2015

when I am trying to perform below query,
 
  INSERT INTO EMPLOYEE
    SELECT TOP 100 *
 FROM EMPLOYEE_LANDING;

I am getting Invalid length parameter passed to the LEFT or SUBSTRING function.

View 3 Replies View Related

Transact SQL :: Window Function To Count Number Of Rows In The Previous 24 Hours?

Nov 16, 2015

is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.

I have a table of events like:

User, TimeStamp, etc...

I want to identify the row which is the event number 50 in the past 24 hours.

does the window functions can do this? and how?

the ROW PRECEDING etc... appear to not have enough feature to support time related function.

Stream-insight is able to create this type of query, but I have to do it directly in SQL.

View 6 Replies View Related

String Matching

Nov 22, 2007

I'm on SQL Server 2005.

I have 2 tables called Customer & CustomerArchive. Both Tables have the same Column names and Datatypes etc.


CustomerID Address Postcode

1 10 Alcove Street 4000

50 21 Spring Street 4032

33 5 Terrace Place 4010

What I need to do is compare each column except for the CustomerID to see if it is the same!

In other words, I need to compare Address in the Customer Table with Address in the CustomerArchive Table.

I have to do this with all columns (12) of them between the 2 Tables.

Is there a efficient & smart way to do this aprt from the obvious way of joing the 2 Tables via the CustomerID and performing a Customer.Address <> CustomerArchive.Address
Customer.PostCode <> CustomerArchive.PostCode
etc etc etc!

View 2 Replies View Related

String Matching Precedence

May 2, 2006

An application filters records based on names found in them. Forexample, looking through property buyer names, looking for banks andrelocation companies.I have a table of names and patterns:CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Categoryint)INSERT #Filters SELECT 'Bank Of America', 0, 1INSERT #Filters SELECT '% Bank %', 1, 2INSERT #Filters SELECT 'Bank %', 1, 2INSERT #Filters SELECT '% Bank', 1, 2INSERT #Filters SELECT 'Credant Reloc%', 1, 3INSERT #Filters SELECT '%Relocation%, 1, 3The filtering matches the table of candidate names against the filters,and returns Category, where the where clause or join is(Candidate = Pattern AND IfWildCard = 0)OR (Candidate LIKE Pattern AND IfWildCard = 1)"Bank of America" matches an exact pattern, and also a wildcardpattern, and the two different matches give different values forCategory. Is there a way to control which match takes precedence, oris necessary to do it multiple times in the desired order, removingthose that hae already matched from consideration?Thanks,Jim Geissman

View 5 Replies View Related

Combine Matching Multiple Rows Into One Row

Jul 23, 2005

IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq

View 2 Replies View Related

Find Matching Sets Of Rows

Apr 11, 2006

Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a

View 7 Replies View Related

Sp_helprotect Error If No Matching Rows

Aug 28, 2006

Why does sp_helprotect throw an error if there are no special security permissions for a specified @username?  Why not simply return a blank resultset?  This makes the procedure a hassle to use when trying to capture results into a table as part of another stored procedure (use it for auditing).

[Edited]The same problem occurs when I execute xp_logininfo to get the list of members for a group that has no members.  Instead of just passing back some sort of status, the procedure blows up and keeps me from scripting the procedure.

Am I just missing something?

Brandon

View 5 Replies View Related

Delete Matching Rows From Target

Apr 27, 2008

Hi

I am very new to SSIS and currently was reading through various manuals but couldnt find a way to do a simple delete.

STAGE_ORDERS looks something like

order_id order_value
100 50000
300 70000
.....

Orders table looks like
order_id order_value
100 9000
200 1000
300 8000

now i want to use SSIS to delete the orders 100,300 which are present in staging table from the orders table .

How do i accomplish this task since i dont see a delete transformation.

How about updates if i want to update the order values from the staging table to the orders table what transformation can accomplish this

regards
Hrishy

View 3 Replies View Related

Transact SQL :: Lottery Winning Numbers Matching

May 6, 2015

I'm developing a Lottery system. Here are some background information:

- Total 48 numbers
- Draw 6 numbers + 1 extra number
- each Bet select 6 numbers

And the Prize:

Prize
Selected Matched
Extra number Matched

1st
6
No

2nd
5
Yes

[Code] ...

Below is my proposed table design (simplified):

Draw table
Column
Datatype

DrawDate
date
Primary Key

ResultNumber1
tinyint

[Code] ....

There will be millions of Bet for a Draw. I need to write a stored procedure to check which bets won the 1st ~ 7th prizes. How to write a query to match the bets with the draw result? The query should be run within 1 minute. And should I change my table design?

View 5 Replies View Related

Transact SQL :: Selecting First Row After Date Matching Condition?

Sep 22, 2015

I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.

The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.

The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.

I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.

I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:

SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...

I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.

View 9 Replies View Related

Transact SQL :: Find Parts Matching In Inventory

Sep 15, 2015

We have an application where we want to check to see if the vehicle part on a job matches to our internal parts inventory (PartsInventory table) before we order it.  The problem is that sometimes the part number matches exactly and sometimes the part number has '-' or space but if those are removed, will match to our internal part number.  Below is what I have so far but it only matches exact part numbers.  One example would be if our part number was 1013738-00-C but the job (in RepairOrderLines) had a part number of 101373800C we should consider it a match.  Both PartNumbers are varchar(30). 

SELECT dbo.PartsInventory.PartNumber, dbo.PartsInventory.PartDescription, dbo.PartsInventory.VehicleMake
FROM dbo.PartsInventory INNER JOIN
dbo.RepairOrderLines ON dbo.PartsInventory.PartNumber = dbo.RepairOrderLines.PartNumber INNER JOIN
dbo.RepairOrder ON dbo.RepairOrderLines.RecordID = dbo.RepairOrder.RecordID INNER JOIN
dbo.Vehicles ON dbo.RepairOrder.VehicleID = dbo.Vehicles.VehicleID AND dbo.PartsInventory.VehicleMake = dbo.Vehicles.VehicleMake
WHERE (dbo.RepairOrderLines.RecordID = 46001)

View 3 Replies View Related

Need A Query To Return Rows Containing ALL Matching Fields

Sep 9, 2004

I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.

For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.

I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".

I hope I am clear enough.
Does anyone have any solutions???

View 2 Replies View Related

How To Find Rows With Matching Numbers In One Table

Oct 19, 2007

Hello All,

I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.

I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.

Any help will be appreciated.
Thanks,

View 7 Replies View Related

Extract INT From String?

Feb 19, 2014

I have a table which has important information stored within a text field as nText. The fields look like this

{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}

The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?

I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.

,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)

I use Microsoft SQL 2008

View 6 Replies View Related

Transact SQL :: How To Join Results Of Two Queries By Matching Columns

Aug 10, 2015

I have two queries as below;

SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;

and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;

How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?

View 4 Replies View Related

Function To Extract Delimited Data

Mar 11, 2008

I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob

View 1 Replies View Related

Delete Rows From A Table Matching A Calculated Date

Apr 15, 2008





Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.

I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.

When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.


Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:

SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

The flat file destination was mainly used to confirm the select.

Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:

DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.

Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?

Thank you and God Bless.








View 9 Replies View Related

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

String Extract From Text

Jun 17, 2014

I have a long text in 'Quote' column as below and i have to extract Trip Duration, Destination and Base Rate from this text. The ‘Base Rate’ will be repeated throughout the text if there is more than one traveler and I only need the first instance.

Begin Quote Calculation<br />
<br />....<br />
Agent Id: 001<br />
Trip Duration: 5days<br />
Relationship Type: Individual<br />....nDestination: AreaTwo<br />
<br ...../>Resolved Trip Type To: 1 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 6.070000<br />.....Resolved Trip Type To: 2 with Trip Subtype: 0<br />
Resolved Relationship: Individual....... />
*Base Rates*<br />
Base Rate: 9.070000<br />.....

Result

Trip Duration: 5 days
Destination: AreaTwo
Base Rate: 6.070000

View 3 Replies View Related

How To Extract Part Of A String

Jul 23, 2005

Is there a function that will extract part of a string when the data youwant does not occur in a specific position?Field "REF" is varchar(80) and contains an email subject line and the emailrecipients contact nameExample data:Rec_ID REF1 Here is the information you requested (oc:JohmSmith)2 Thanks for attending our seminar (oc:Peggy SueJohnson)3 Re: Our meeting yesterday (oc:Donald A. Duck)What I need to extract is the contact name that is in parenthesis after theoc:The name is always in parenthesis and occurs immediately after "oc:" - nospaces after the "oc:"Thanks.

View 4 Replies View Related

How To Extract Part Of String

Aug 22, 2007

Hi,


I have 2 questions.

1. I have a table with a column for region names. Region Names are in 2 formats basically - "NAME-BU*RM" OR "NAME*RM".
I want to extract just "Name" from this string.
The length of "Name" varies and I want to extract all characters included for "Name".
Can anyone advise what the query/SQL statement would look like?



2. I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.
So I want to include a code that checks if file "file.xls" is open on user's machine.
If file is open, then message "file "File.xls" is already open. Generating File_1.xls"
Run the code but create the file with file name "file_1.xls"
If file doesn't exist, then run code and create file with file name "File.xls"

Please advise.

View 8 Replies View Related







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