Function To Extract Delimited Data
I'd need to have a function that allows me to extract 'fields' from
within the string
I.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 string
select EXTRACT(@foo,'*' , 9) ;-- would return null
Extract( string, text delimiter, occurance)
Anyone have something like this as a user defined function in SQL?
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'.
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number.
I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server.
Thanks in advance.
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination : Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk
I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV
SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM
I have the following data example,
Forest Ridge Dr
I wish to achieve the following,
Forest Ridge Dr
I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
But i get the following result which is no good!
What am I doing wrong in the Street & StreetSuffix Functions above?
I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.
In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))
SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))
SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))
I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:
movie_title | movie_rating | category_name--------------------------------------------------------------------------------------------------Pirates of the Carribean | PG-13 | Action,Adventure,FantasyEvil Dead | Unrated | Horrorand so on ...I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift? It all seems counter-intuitive. Thoughts?
Im interfacing with an app that imports/exports data in a very specific hash-delimited format
How do i import this into a dataset so i can in my db.
Obviously ideal way would be to use the Jet text provider, but im not sure if the schema.ini can be setup to deal with this kind of data.
If anyone has ANY way of me getting this data into dataset please help.
I need to export data from SQL server 2000 database into text file uisng ç Delimited. Because my destination database will be teradata. Could you let me know if you have any method for this.
I have a table in which I want to create a delimited list of values from one field which I will be using for validation.
How can I do this without using a cursor to build the string. The SQL would be something like:
WHERE SourceTable = 'FieldValueList'
I'm looking to return on string like -
I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file.
So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited. I tried finding examples but couldn't see what I should do to have a comma delimited file. (I'm getting a bit tired now, so I might be missing something!)
I have created a bat file containing the following code:
bcp "TestDB..GA_FSM_DCSF_Extract" out "C:GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:error.log" -c -T -S srckvzg2j -r
Any help / pointers would be much appreciated.
Hi, I'm hoping someone has an idea or two on this topic.
Basically I have three tables of data say tContact, tQuestion, tAnswer
I need to extract the data for the client and they would like to see
the data with one line per contact, but showing every answer to every
question... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc........
Obviously to get the data I cansimply do an outerjoin to get all
contact data then all questions, and answers that exist... but that
will obviously return tabular data with one row per each
answer... Does anyone have any ideas on how to do this using just
SQL? I can pull the data and write a function that spits it out
to text using the Stringbuilder class and some logic, but I'm thinking
this must be possible in SQL natively... any help would be more than
appreciated. Thanks in advance.
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
Hi,Anybody have an idea of copying data from tables of a database to anotherdatabase. It should be a choice to select all tables, single table orseveral tables.For them knowing Oracle it is possible to do it with 'exp', where you canchoose to script the database with or without data. So I am trying to getalike to MSSQL. The job is to unload data from a database with onestructure to another database with another structure.Thanks in advanceBest regardsTom Frank
Does anyone know how to directly extract data from a SAS database (Verison 6.12 or V8)?
Currently I run a SAS program which exports a tab delimited file. I then build a local package to create a SQL 7.0 Table.
i have a field name(fil_srt_cond) with the values of
i used the char index:-
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str
to display them in their specific fields like
NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC
however for cmpnt_stuff,DESC,2,3 i'm getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES
therefore i'm not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance
I've two table.......
One table Name Form1
Second table Name Form2
i want top 3 Name(field) in first table(Form1).
I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.
I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?
Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3
Please help. Just can't figure this out!
Hello, i need to load some data from a long comma delimited text file, How can a i do that, using t-sql?, thanks for your help!!!!!
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl(
salesreplist [text] NULL,
reprate1 int NULL,
reprate2 int NULL,
reprate3 int NULL,
reprate4 int NULL,
reprate5 int NULL,
Here is some sample data:
1 A 0 0 0 0 0
2 0 0 0 0 0
3 I~~~~ 15 0 0 0 0
4 A~B 5 5 0 0 0
5 I~F~T~K~G 5 5 2 2 2
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
Can anyone suggest a solution?
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1. Id = 1
2. GraphPoint = 023 (float data type)
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
1 12, 45
2 10, 20
and I would like to have results as
I would appreciate if someone could offer me some guidance here.
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance.
I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3.
FIELD 1 FIELD 2
I would like some information on extracting data from an ODBC connected database. (oracle is the current master database).
I can connect to the oracle database, and view tables.
But I would like to create a job (in SQL server) that runs every hour to retrieve current data from the oracle database.
Has anyone accomplished this, or is there a tutorial about this procedure?
Any help would be appreciated....