Having 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
Table1 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...
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
<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
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 ?
If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
I 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.
I 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 |
I'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?
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.
I am trying to process an XML document that contains the attribute 'from_x'. However an openxml query can't seem to find any column with a '_x' suffix. For example if I were to execute the following fragment:
What is parsing?? can someone give me an example please?? this what I got from BOL
Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.
I am new to SQL server and was wondering if someone can help me with this one. Thanks My table holds 2 columns (SECTOR and TERM) with following example values
SECTOR TERM Hybrid 6/18 Hybrid 9/19 Hybrid 10/17 Hybrid 3/13
I would like to find out the rows where my values from SECTOR before '/' does not equal TERM
Can you parse a SQL field? Let's say, FULLNAME field got a TEXT datatype with the following data: <firstname>Norm</firstname><lastname>bercasio</lastname><Color>blue</color>then using a select statement, parse the field to find the lastname then write it to another field called LASTNAME on the same table, same rowID. Can you send a select statement how it can be done? I am using SQL 2003 or 2005. thank you so much.
I'm using a SQL selection to fill a DataGrid. One of the fields I have is called diagnosis. This field in the database can contain multiple diagnosis. But I use a set of characters to divide each diagnosis. Example : Sick!@#$%Hurt!@#$%Ill!@#$% My problem is this is how it looks in my Data Grid. Can someone tell me how to parse out each diagnosis.
How to remove same repeated string in a column per row from a table? Looked at replace, stuff string functions, but none take a column name as a parameter.
Anybody out there ever take a column containing names and parse it out to salutation, first name, middle initial/name, last name, suffix using Transact-SQL? I think I know how to do it using an array in a procedural language, but using SQL I'm drawing a blank.
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like: User Answer 1 Good,Fair,Good,Bad 2 Bad,Good,Good,Good 3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
I have a large file of over 40k email records. The emails are all mixed up and come in various formats but i noticed that most of them are in this format:
For all those emails with the period (.) in between, the (.) actually separates an individuals first and last name.
My task is this, to separate all the emails that are in this format into first and last name fields. I'm stimped folks and I'll really appreciate any pointers or ideas on how to go about solving this task.
Parsing Address This is not really a reply, but I saw the problem and the replies look very promissing. I'm using ss2k, I have a table with an address column. here is some example of the records under ADDRESS :
WILLOW CREEK PL RED BARN DR RED BARN DR CARRINGTON DR RENNER RD EDMONTON CT SPRINGBRANCH DR HILLROSE DR CEDAR RIDGE DR LARTAN TRL PRESIDENT GEORGE BUSH HWY
What I want to do is to write script that runs daily and parse the street names (RED BARN) and street types (Dr, PL , etc.. ) to 2 colums. As u can see there is no fixed length or fixed number of words ...etc ... Any help would be really appreciated. thnks
can anybody translate to Transact SQL specifically the example of create function elemIdx i didnt understand how he used recursion may b cuz the language is odd to me i didnt get it
Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making. (This works on my test SQL2005 server)
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[SecurityEvents].* FROM [SecurityEvents] JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID] WHERE [SID] NOT LIKE 'S-%'
Query Output: quote: Server: Msg 170, Level 15, State 1, Line 28 Line 28: Incorrect syntax near 'Strings'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'Strings'.
Hi, I have a name field that has fullname in it and i need to parse it to firstname, lastname, middle initial and suffix. I used the below query to parse the Last Name and first name.
SELECT SUBSTRING([FullName], 1, CHARINDEX(' ',[FullName]) - 1)AS LastName, SUBSTRING([FullName], CHARINDEX(' ',[FullName]) + 1, LEN(FullName)) AS FirstName FROM CustomerTbl
There are middel initials and suffix present in the full name. How do i parse the middle initial and suffix.
The Sample Name that appear in the table: JONAS VICKY ==> Format without Middle Initial or suffix MAYES MARY T ==> Format with middle initial MCGEE, III CLIFTON ==> Format with suffix
