Search String And Extract To Another Table
May 4, 1999
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
View 10 Replies
ADVERTISEMENT
Jul 22, 2013
I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
View 6 Replies
View Related
Apr 29, 2015
Currently we have requirement like below.
Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"
In the DB i have column values like below.
FileType Val
FILENAMETYPE Direct
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
View 10 Replies
View Related
May 16, 2007
Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp
View 1 Replies
View Related
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
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
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
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
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
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
Aug 29, 2013
Creating a stored procedure in extracting a word in a sentence.
Example:
String=The quick brown fox JuMp over the lazy dog.
I want to extract a random word on the string.
View 1 Replies
View Related
Jul 20, 2005
Is there anyway to extract part of a string in a stored procedureusing a parameter as the starting point?For example, my string might read: x234y01zx567y07zx541y04zMy Parameter is an nvarchar and the value is: "x567y"What I want to extract is the two charachters after the parameter, inthis case "07".Can anyone shed some light on this problem?Thanks,lq
View 4 Replies
View Related
Oct 15, 2015
I have a values a column like this
Morning(07:00-16:30)
Morning(09:30-18:30)
Morning(11:00-20:00)
Afternoon(14:00-23:00)
Afternoon(16:00-01:00)
Morning(08:00-17:00)
Morning(10:00-19:00)
Morning(09:00-18:00)
So i just only need 07:00-16:30 this kind of part from above all these string in sql server.
View 4 Replies
View Related
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
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
May 11, 2015
Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
View 2 Replies
View Related
Jul 21, 2015
I want to extract a number within a column results, that is the number between the first two commas
Example:
select path from tablea
results:
1158285,1158286,1158287,1158288,1158289
1158288,1158289,1158290,1158291,1158292
....
How to extract the second number(between the first two commas) from the above results?
The output should be
1158286
1158289
View 2 Replies
View Related
Mar 16, 2004
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END
View 4 Replies
View Related
Feb 11, 2008
Hello forum, I need extract a substring form a string that follows a regular expressions -->
T:1º/PQ:1/TALLA:2(MOD.51 100/150)
T:<number>º/PQ:<number>...
I need to extract only the numbers. The SUBSTRING functions is insuficient for me, because in some cases de number can be 1, 10, 100, 1000, ...
I would like me use it as:
SELECT RegularExpression (Field)
Can you help me, please.
Thanks for all in advanced. I will appreciate a lot your help.
View 3 Replies
View Related
Jan 11, 2008
I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_
I want to select the 8 characters after the first comma in the field, and then convert it to a date format.
I am a novice, so any help with the correct function and syntax would be greatly appreciated.
thanks,
Dave
View 8 Replies
View Related
Jul 9, 2014
I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
View 9 Replies
View Related
Jun 18, 2015
Row Value :
Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun
and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.
Should I just go for charindex on break() or is there a another way ?
View 3 Replies
View Related
Apr 21, 2014
I am trying to extract the IP address from a command string, the problem I am running into is there are 3 types of command strings.
SENDAUTO IP192.168.1.32L0O11 Z1 5(E=00:00,F=00:00,G=00:00,H=00:00,I=00:00,J=00:00)
SENDCREATEEXCEPTION -1,IP192.168.1.32,0,11,0,Z1,Free text-label,19,3,19,3,06:00|24:00,I|O,1,288003,1
SENDWEEKTIMES IP192.168.1.32,0,11,Z1,3,100,23:00|24:00|24:00|24:00|24:00|24:00,I|O|O|O|O|O
The IP address length can vary, and the character after the IP is either a L or ,
How can I edit the following function to look for both characters?
SUBSTRING(Command, CHARINDEX('IP', Command), CHARINDEX('L', Command) - (CHARINDEX('IP', Command)))
View 6 Replies
View Related
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
View Related
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
Feb 27, 2008
I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.
My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.
The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.
So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.
I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.
I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.
View 4 Replies
View Related
May 29, 2015
I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.
=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),", ",","),",")
The value will look like this
01-Manger
02-Senior Mange
21-Associate
25-Associate Trainee
This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()
=SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",
",","),1,2),",")
Result will be 01
But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.
View 5 Replies
View Related
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
Sep 12, 2007
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
View 5 Replies
View Related
Jan 14, 2006
I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance
string strCatalog = "TestCatalog", strsearchstrings=""; string strQuery=""; strsearchstrings = txtsearch.Text;
strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')"; // TextBox1.Text is the word that you type in the text box to query by using Indexing Service. string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring); conn.Open(); System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn); System.Data.OleDb.OleDbDataReader objRdr;
objRdr = objcmd.ExecuteReader(); DataGrid1.DataSource=objRdr; DataGrid1.DataBind(); objRdr.Close(); conn.Close();
View 1 Replies
View Related
Mar 30, 2013
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
View 14 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Mar 25, 2015
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
View 0 Replies
View Related
