Data Parsing In SQL Server Views
Jul 23, 2005If column1 in SQL Server column is text: 19980701
What is the syntax in the select statement to convert it to a date
like: 07/01/1998
Thanks for any help
Rbollinger
ADVERTISEMENT
SQL Server 2008 :: Parsing Data To Select Certain Values From XML
Mar 13, 2015I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.
example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />
I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.
Not really sure where to start. Table is GAME, and column is XMLDATA.
Parsing Data
Apr 21, 2000Do anyone know of any functions I can use to parse the following data eg.
M 3480-7 should be 3480
M 3477-19 should be 3477
M 28-10 should be 28
Thanks in advance,
Vic
Parsing XML Data
Nov 7, 2003I have a SQL Server table that holds XML documents. Is there a known SQL Server XML parser ?....how can I export XML data into a readable format ?
thanks
Parsing Data
Sep 13, 2005Does anyone know any good URL's for examples on parcing data using SQL?
As an example, i've got First/Middle/Last name of a person inside a single field, I want to turn that into 3 fields.
Thanks!
Caden
Parsing Data...
Mar 5, 2008Table1 includes a column for email address. I'd like to parse everything up to 8 chars before the '@' symbol in this column. My script doesn't appear to be working, and I'd appreciate any assistance in troubleshooting it!
select substring(UPPER(substring(email,1,charindex('@',email)-1)),1,8)
from table1
The error produced is;
Invalid length parameter passed to the substring function.
What am I doing wrong? The logic in the script looks good to me...
SQL XML :: Parsing Xml Data
Jul 29, 2015Having never used SQL for anything serious I have been asked to look at importing multiple data files into a database and have come to a dead stop. The XML file is a repeating structure with but the number of <SampleVial>s and the number of <Particles>s in each file is a variable...example shown below
<AcquisitionIntervals>
<SampleVial id="1">
<AcquisitionInterval>
<StartOfIntervalUpdate>
<Id>0</Id>
[code]....
I need to pull all of the particle data associated with each <SampleVial>.
Help With Parsing Data
Apr 28, 2008This is what I have:
Field1
bob johns 555-123-5678
barry molsen 111-222-3333
333-444-5555 mary smith
566-234-0987 randy miller
This is what I want to have:
Field 1
Field 2
555-123-5678
bob johns
111-222-3333
barry molsen
333-444-5555
mary smith
566-234-0987
randy miller
I am looking for a way to take information from one and split it into 2 fields.
We are currently using one for two different type of information. Name and phone#. The info in the original field is mixed with the name first sometimes and other times the phone # is first.
Can you think of a way to using sql to separate this data into 2 fields?
There are 30,000 rows with the info like this.
We are using sql server 2005
Thanks
Blair
SQL String Data Parsing
Sep 16, 2007<p>
Hi everybody, I was hoping to get some advice something I can't quite get my head around. I have a SQL db which contains a table with ratings using the AJAX rating control.
When someone rates an object, I need to select the current rating and then use those numbers to;
- calculate the new average
- add new score to total score
- increment number of votes by one.
I thought this can be best achieved using the SELECT statement and then parsing the SELECT string. (is the string comma separated?)
using each array, i'd need to convert this into integers and then do the calculation. and re-upload the data to the ratings table (using the UPDATE statement).
Is this the best way of proceeding? I have tried initially to write the code using three sql statements. But that would mean to many requests from the server, right?
Below is the conde I have writting already.int myrating;
myrating = Rating1.CurrentRating;string getscore = "SELECT " +
"RatingScore" +"FROM Rating " +
"WHERE ItemID= '" + _ItemID+ "'";string getcount = "SELECT " +
"RatingCount" +"FROM Rating " +
"WHERE ItemID = '" + _ItemID + "'";string getaverage = "SELECT " +
"RatingAverage " +"FROM Rating " +"WHERE ItemID = '" + _ItemID + "'";
int _ratingscore;int _newscore; _ratingscore = int.Parse(getscore);
_newscore = _ratingscore + myrating; //add new rating score to old scoreint _ratingcount;
int _newcount;_ratingcount = int.Parse(getcount);
_newcount = _ratingcount + 1; //increase count by 1int _ratingaverage;
int _newaverage;_ratingaverage = int.Parse(getaverage);
_newaverage = _newscore / _newcount; //calculate new average rating
otherwise
otherwise would i be best off to do the following?...
string[] dbRatings = SQLstring.Split(',');
??
Any help would be appreciated.
Many thanks in advance.
Phil
</p>
Parsing Data Question.
Apr 24, 2000Can some body please give me the correct syntex for parsing data from the
following alphanumeric field: 0014990328
I want to get the 3rd number/character and put it in a separate field.
I also want to get the 4th number/character and ...
I tried substring( column.. but nt getting the correct result.
Thanks in advance.
M.Khan
Parsing Varbinary Data In T-SQL
Apr 11, 2008Hi,
We serialize a custom object into a byte array (byte[1000]) and store it in a SQL Server 2005 table column as varbinary(1000).
There are a lot of rows retrieved with each SqlDataReader from C# code: up to 3,456,000 rows at a time (that is, roughly 3.5 million rows).
I am trying to figure out what will be more efficent in terms of CPU usage and processing time.
We have come up with quite a few approaches to solve this problem.
In order to try a few of them, I have to know how I can extract certain "pieces" of data from a varbinary value using T-SQL.
For example, out of those 1000 bytes, at any given moment we need only the first 250 bytes and the third 250 bytes.
Total: 1000 -> [250-select][250-no-need][250-select][250-no-need]
One approach would be to get everything and parse it in C#: get the 1st and the 3rd chunks of data and discard the unneeded 2nd and 4th.
This is WAY TOO BAD.
Another approach would be to delegate the "filtering" job to SQL Server so that SqlDataReader gets only what it needs.
I am going to try a SQL-CLR stored procedure, but when I compared performance of T-SQL vs. SQL-CLR stored procs a few weeks ago, I saw that the same job is done by T-SQL a bit faster AND (more importantly for us) with less CPU consumption than SQL-CLR.
So, my question is:
how do I select certain "pieces" of varbinary column data using T-SQL?..
In other words, instead of
SELECT MyVarbinary1000 FROM MyTable
how do I do this:
SELECT <first 250 from MyVarbinary1000>, <third 250 from MyVarbinary1000> FROM MyTable
?
Thank you.
Parsing A Field Data And Populating Other Fields
Nov 3, 1999I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
Thanks for your help!
Scott
Parsing The Data From One Column To Individual Columns?
Aug 16, 2012I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |
MSH|^~&|HL7ADM|PYXIS|PAH|HL7PRX_PAH|201208131129 46||ZPM^ZPM|9442|P|2.3.1||||||| ZPM|C|console|N-HEART-ST|1|23|43025204|DOBUTAMINE 1000MCG/ML|U|1|1|1|CS3808|TEST, MONKEY|||1|||||0|1|0|20120813112839||||||||0||IV|| |||||||||||
SQL Server 2014 :: Replicating Tables Referenced By Indexed Views With Data Binding
Apr 1, 2015when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way
View 1 Replies View RelatedSQL 2012 :: Parsing Out Data String With Text Delimiters
Nov 5, 2014I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
SQL 2005 - .Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 - Error Parsing Statements
Nov 2, 2006Hi,
Does anyone else have this error message pop up in SSMS when you try to parse sql statements:
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
There was a thread back in March 2006 that mentioned this error, but the posted resolution was to install SP1. I have SP1 installed but I still get the error.
I only receive the error when I'm parsing statements, if I run the statement it's fine.
Thanks
Matt
SQL Server Syntax Parsing
Feb 23, 2008Hi All,
In SQL Studio, there is a really handy option to parse your query prior to running it (that little green tick button next to Execute).
I dont suppose there is a similar thing that can be used on the command line at all is there??
Cheers
-
Karym6
Best Practice - Data Sources And Data Views Vs OLE DB Source
Feb 26, 2008Hi, i'm wondering which is the best way to search data in a SQL Server.
I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query.
I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices?
Thanks for your help.
Beli
SQL Server 2008 :: XML Query Parsing
Mar 9, 2015I want to take this XML and put it into a table with CustomerId and MatchingSetId. With this SQL, each MatchingSetId gets assigned to each CustomerId instead of retaining the relationships in the XML.
declare @myXML XML = '<CustomerMatchings>
<CustomerRecord CustomerId="10600">
<MatchingSetId>11</MatchingSetId>
<MatchingSetId>13</MatchingSetId>
<MatchingSetId>18</MatchingSetId>
<MatchingSetId>23</MatchingSetId>
[code]....
SQL Server 2008 :: Parsing Out XML On Same Level
Aug 20, 2015I am trying to parse out
<Discharge_x0020_Time>
<time>
<Hour>11</Hour>
<Minute>:00</Minute>
<AM_x002F_PM>AM</AM_x002F_PM>
</time>
</Discharge_x0020_Time>
Into Hours, minutes, and ampm
I use
Select...
,DISCHARGEHOUR.value('(./Discharge_x0020_Time/time/Hour)[1]', 'varchar(10)') AS [hour]
,DISCHARGEMINUTES.value('(./Discharge_x0020_Time/time/Hour:minute)[1]', 'varchar(10)') AS [Minutes]
,DISCHARGEAMPM.value('(./Discharge_x0020_Time/time/Hour/minute/AM_x002F_PM)[1]', 'varchar(10)') AS [ampm]
FROM ...
CROSS APPLY data.nodes('/Data') a(DISCHARGEHOUR)
CROSS APPLY data.nodes('/Data') b(DISCHARGEMINUTES)
CROSS APPLY data.nodes('/Data') b(DISCHARGEAMPM)
But minutes AND AMPM come up as NULL I assume I am setting up something wrong with the level on minutes AND AMPM. Also, can I disregard the ":" in the minutes.
Are Embedded Views (Views Within Views...) Evil And If So Why?
Apr 3, 2006Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedSQL Server 2012 :: Parsing XML That Does Not Quite Fit Normal XML Standards
Jan 21, 2015I have an application that stores xml data in an unusal manor. Basically a SQL Key column and an XML string.The XML string is not really standard XML, but it is what it is, and I'm stuck with it. It is in the format;
<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>
I am able to pull values out via
SELECT
p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1,
p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2
FROM dbo.UserXMLTable
CROSS APPLY XMLRECORD.nodes('/row') t(p)
where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
However I've been struggling with selecting row with a LIKE clause. Something like ;
SELECT *
FROM dbo.F_UserXMLTable
where XMLRECORD.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
I have tried a number of permutations of XML syntax but so far have been stumpled.
Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .
SQL Server 2008 :: Parsing Unstructured CSV File?
Oct 1, 2015I have a CSV file with roughly 6 million rows. The file is unstructured; that is, some rows have 5 fields, others have 15, and there are as many 50 fields in one row.
I am using bulk insert to read the entire file into a table in database, with each row being a database record. With that, I have one column that contains a row of comma delimited fields. All fields are character string and I want to find a quick way of parsing each row and placing each comma-delimited value in a column. For example:
CREATE TABLE MyTable
(
CSVString varchar(1000),
C1 varchar(20),
C2 varchar(20),
...
C50 varchar(20),
)
Column CSVString contains the a CSV row (I don't know how many filelds (no. of commas + 1) in the row, but if the row contains 10 fields, I need to populate columns C1-C10. If the row has 15 fields, I populate columns C1-C15.
How can I do this in a very efficient way? I tried CTE but performance was not very good.
SQL Server 2008 :: Text String Parsing To Apply Operators To Datasets?
Aug 7, 2015I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
Partition Data/Views?
Jul 17, 2001Hi folks,
Recently i've been working on a new project that would partition a large table 2 smaller tables. I then create a view to union the 2 smaller tables(table A, B). I've been getting a strange error when i try to update, insert, delete a record through the view. "View needs partitioning column"....i find this strange. Both of my table have a cluster primary key consisting of 3 columns, and one of the 3 columns(date field) consist of a check constraint. The constraint is used to determine what record goes into which table. Am i missing anything else? The really strange part is sometime it works, and sometimes i get the error message.
Any thoughts?
Joe R.
Adding Data Through Views ?
Dec 8, 2004Hi!
Is it possible to add/delete/modidy data through views?
What is the major difference between SQL Stored procedure & SQL function?One diff i know is using SP we can execute transact-sql statements.Is there any other difference?
Thanks in advance
Editing Data Using Views
Feb 29, 2008Please direct me towards good documentation about updating, inserting and deleting data using views involving multiple tables.
Thanks
Retriving Data From 2 Views By The Value In A Textbox
Aug 9, 2007i have a textbox which a user enters a numeric value
i want it to use SqlDataSource and check if the value exists in any of the tables.
in my text box the users would enter starting from '100000' or '200000'
i want it to check the view that starts the # with '100000' and 2ed view starts '200000'
With this i can check in one of the tables and make the selection.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"
SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)">
<SelectParameters>
<asp:ControlParameter ControlID="txtReport" Name="ReportNumber" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
How can i make this possible ?
i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?
Reading Data From Table And Views
Feb 2, 2006Hi
I need to read a very big table more than 60,000 record but it is giving the following problem: But if it is small table there is no problem. Same problem also views.
Server Error in '/POBuilds' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
Very Urgent : Bcp Or Dts Data To Partitioned Views
Jan 1, 2004someone please tell me if i can bcp or dts data in to partition views
it is failing with the error the bulk operation does not support this
Comparing Data In Tables/views.
May 13, 2008I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
Querying Data From Multiple Views
Jul 23, 2005Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
View 1 Replies View RelatedUsing Views/Stored Procs In One Db, Data In Another
Jan 24, 2007I am moving an app from Access 2003 to C#/SQL Server 2005. The Access app has one front end exe and two back end databases. One back end db is for UK users, one for US users. The tables and structures are identical, but the data is different. UK users link to the UK back end, US users to the US backend. (The queries are in the front end)
In SQL Server, the view and stored procs will be in one database (KCom), the US data tables in another database (KUS), and the UK data tables in another (KUK). All databases are on the same server.
My question is how to let the views and stored procs in KCom know whether to pull the data from KUK or KUS.
In the front end app, I use ADO.Net to deal with the SQL Server databases.
I have not been able to find a model for this, but it must be somewhat common.
I willl have a lost of nested views and stored procs, but as a simple example, say I have a view in KCom which is just "Select * From Invoice Where InvDate > '01/01/2007'. The ADO request would come from the front end app which would know whether it wanted the data from KUK or KUS. How would I get the view to get the data from one as opposed to the other (KUK vs KUS) ?
Is there some other strategy for this situation, say use of connection strings? If anyone has an idea, or knows of an explanation somewhere on the net I'd greatly appreciate it.
Many thanks
Mike Thomas