Hi everyone,
Thanks in advance for reading my mail. I have a query that returns a column consisting of numbers. However the numbers are all in String format so when I get the max value it returns the one with the largest first digit.
e.g. if it returned the values '5','7','9','20' and ordered them it would return 9 as the max value. Is there anyway to parse these to ints and then get the max.
Sorry for posting twice,
didn't get the chance to say thanks,
Mark.
Hi everyone, Thanks in advance for reading my mail. I have a query that returns a column consisting of numbers. However the numbers are all in String format so when I get the max value it returns the one with the largest first digit.
e.g. if it returned the values '5','7','9','20' and ordered them it would return 9 as the max value. Is there anyway to parse these to ints and then get the max.
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.
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 again, When developing web sites with Asp, DreamWeaver and Access, I've always used Unique IDs for setting things like userID or productID. However, with VWD Express and a .mdf db, this doesn't seem like an easy thing to do, at least not when inserting test data during development. The reason is that the uniqueIDs are 32 or so digits, and I have no idea what to write, and it's tiresome anyway. I used a book from Wrox (Begninning Asp.Net 2.0) to learn VWD and Asp.Net, and in their examples they actually use ints for setting their productIDs and the like. I'm really surprised to see that in a text book, but that's the way they've done it. So what's the best practice? What should I do? Please help me out here as I'm really confused! Thanks in advance, Pettrer
I have an instead of trigger for update on particular table which adds3 or so columns and puts the total in a 4th No matter what the input is(as long as there is one NULL) the total is always NULL. More thanlikely is that only one of the fields has a value in it and the restnull but i don't want to do the MAX in case that is not true.a quick visual aid from input and desired outputInput DatacolA colB ColC tot5 NULL NULL NULLCurrent OutputcolA colB ColC tot5 NULL NULL NULLDesired OutputcolA colB ColC tot5 NULL NULL 5How do i Manipulate the TSQL command to work correctly?Update tbl_lossChecksSETclc_totalFees = Inserted.mny_LegalFees + Inserted.mny_AdjusterFees +Inserted.mny_Expense ,
Rather than the real code, here's a sample we came up with.
Here's the C# Code: public class sptest : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label1; private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here string strSP = "sp_testOutput"; SqlParameter[] Params = new SqlParameter[2]; Params[0] = new SqlParameter("@Input", "Pudding"); Params[1] = new SqlParameter("@Error_Text", ""); Params[1].Direction = ParameterDirection.Output; try { this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params); Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString(); } //catch (System.Data.SqlClient.SqlException ex) catch (Exception ex) { Label1.Text = ex.ToString();
} }
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS SET @Error_Text = 'Test'GO When I run this, it prints up the input variable, but not the output variable.
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.
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
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
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...
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M
hi..i need help for my exam, I retrived data from my sms inbok andinsert to table.The record just like this:ID1,12x100,56x12,90x90..,how to pars the table in to:ID First SecondID1 12 100ID1 56 12ID1 90 90Thank for your help...===================
