|
This data is in SQL 2000 and the Field Name is raw_payload I want to write a query that seperates this field by the '+' as a delimiter into 4 other fields a1 a2 a3 a4 or whatever in the same database. Any help would be awsome.
raw_payload
S9000+
UV+100.0000+0.0000
UV+101.0000+101.0000
UV+102.0000+64.0000
UV+103.0000+0.0000
UV+104.0000+
UV+105.0000+0.0000
UV+106.0000+3.0000
UV+107.0000+0.0000
UV+108.0000+0.0000
UV+109.0000+0.0000
UV+110.0000+0.0000
UV+111.0000+0.0000
UV+112.0000+1494.0000
UV+113.0000+38.6623
UV+114.0000+66.0000
UV+115.0000+8.1340
UV+116.0000+0.0000
UV+117.0000+0.0000
UV+118.0000+0.0000
UV+119.0000+0.0000
UV+120.0000+0.0000
UV+121.0000+0.0000
UV+122.0000+0.0000
UV+123.0000+0.0000
UV+124.0000+0.0000
UV+125.0000+0.0000
UV+126.0000+0.0000
UV+127.0000+0.0000
UV+128.0000+0.0000
UV+129.0000+0.0000
UV+130.0000+0.0000
UV+131.0000+0.0000
UV+132.0000+0.0000
UV+133.0000+0.0000
UV+134.0000+0.0000
UV+135.0000+0.0000
UV+136.0000+0.0000
UV+137.0000+0.0000
UV+138.0000+0.0000
|
|
|
|
|
This is best handled outside of TSQL since arrays are not a native element in SQL. Therefore, nothing to split the string into.
You can cludge a query using CHARINDEX and SUBSTRING. You will need to add some CASE statements to deal with any missformed data.
|
|
|
|
|
Right now I am exporting from SQL 2000, importing this into Access 2003 exporting out to Excel 2003 parsing the data with macros and re importing to Access to run my reports wanted to reduce all the importing and exporting if possible to speed up the process....
|
|
|
|
|
It can be done - it will be quicker. The code will be look clumsy.
|
|
|
|
|
try this, I guess it will help you.
<br />
select *,left(raw_payload,charindex('+',raw_payload,1)-1),<br />
left(substring(raw_payload, 1-len(left(raw_payload,charindex('+',raw_payload,1)+1)), <br />
len(raw_payload)),charindex('+',raw_payload,1)* charindex('+',raw_payload,1)-1 ),substring(right(raw_payload,charindex('+',raw_payload,1)*3),charindex('+',right(raw_payload,charindex('+',raw_payload,1)*3),1)+1,len(raw_payload))<br />
from table2
I Love T-SQL
modified on Monday, April 21, 2008 5:52 PM
|
|
|
|
|
The left works all the way down the right starts to include the middle and the + sign delimiter,
The data as it goes down increases the characters and some times it has bad data or junk I would like it to delete if it does not match the standard. field one descrption=text field two usage=number and field three adjustables=numbers
Below are the want it and the way it is...
THE WAY I WANT IT
DESCRIPTION USAGE ADJUSTABLES
UV 195 0
UV 196 2
UV 197 0
UV 198 0
UVN 500 1
UVN 501 1
UVN 502 0
VN 509 -571
UVN 510 -377
UVN 511 0
UVN 512 -575
OTLWV 513 -296
OTLWV 514 -35
OTD/ROV 515 112.4570
OTD/ROV 516 12.7000
THE WAY IT IS IN SQL
"S9000+"
"UV+ 131.0000+ 0.0000"
"UV+ 132.0000+ 0.0000"
"UV+ 133.0000+ 0.0000"
"UV+ 134.0000+ 0.0000"
"UV+ 135.0000+ 0.0000"
"UV+ 136.0000+ 0.0000"
"UV+ 137.0000+ 0.0000"
"UV+ 138.0000+ 0.0000"
"UV+ 139.0000+ 0.0000"
"UV+ 140.0000+ 0.0000"
"UV+ 141.0000+ 0.0000"
"UV+ 142.0000+ 0.0000"
"UVN+ 524.0000+ 0.0000"
"UVN+ 525.0000+ 0.0000"
"UVN+ 526.0000+ 0.0000"
"UVN+ 527.0000+ 0.0000"
"UVN+ 528.0000+ 0.0000
"TLOV+2044.0000+ 0.0000"
"TLOV+2045.0000+ 0.0000"
"TLOV+2046.0000+ 0.0000"
"TLOV+2047.0000+ 0.0000"
"TLOV+2048.0000+ 0.0000"
"TLOV+2049.0000+ 0.0000"
"OTLWV+2201.0000+ 0.0000"
"OTLWV+2202.0000+ 0.0000"
"OTD/ROV+2401.0000+ 0.0000"
"OTD/ROV+2402.0000+ 0.0000"
"OTD/ROV+2403.0000+ 0.0000"
"OTD/ROV+2404.0000+ 0.0000"
"OTD/ROV+2405.0000+ 0.0000"
"OTD/ROV+2406.0000+ 0.0000"
"OTD/ROV+2407.0000+ 0.0000"
"OTD/ROV+2408.0000+ 0.0000"
"OTD/ROV+2409.0000+ 0.0000"
"OTD/ROV+2410.0000+ 0.0000"
"OTD/ROV+2411.0000+ 0.0000"
"OTD/ROV+2412.0000+ 0.0000"
"OTD/ROV+2413.0000+ 0.0000"
"OTD/ROV+2414.0000+ 0.0000"
"OTD/ROV+2415.0000+ 0.0000"
"OTD/ROV+2416.0000+ 0.0000"
"OTD/ROV+2417.0000+ 12.7000"
"OTD/ROV+2418.0000+ 0.0000"
"OTD/ROV+2419.0000+ 0.0000"
"OTD/ROV+2420.0000+ 0.0000"
"OTD/ROV+2421.0000+ 17.0000"
"OTD/ROV+2422.0000+ 17.0000"
"OTD/ROV+2423.0000+ 17.0000"
"OTD/ROV+2424.0000+ 17.0000"
"OTD/ROV+2425.0000+ 0.0000"
"OTD/ROV+2426.0000+ 0.0000"
OTD/ROV+2427.0000+ 0.
"OTD/ROV+2428.0000+ 0.0000"
"OTD/ROV+2429.0000+ 0.0000"
"OTD/ROV+2430.0000+ 0.0000"
"OTD/ROV+2431.0000+ 0.0000"
"OTD/ROV+2432.0000+ 0.0000"
"OTD/ROV+2433.0000+ 0.0000"
"OTD/ROV+2434.0000+ 0.0000"
"OTD/ROV+2435.0000+ 0.0000"
"OTD/ROV+2436.0000+ 0.0000"
"OTD/ROV+2437.0000+ 0.0000"
"OTD/ROV+2438.0000+ 0.0000"
"OTD/ROV+2439.0000+ 0.0000"
"OTD/ROV+2440.0000+ 0.0000"
"OTD/ROV+2441.0000+ 0.0000"
"OTD/ROV+2442.0000+ 0.0000"
"OTD/ROV+2443.0000+ 0.0000"
"OTD/ROV+2444.0000+ 0.0000"
"OTD/ROV+2445.0000+ 0.0000"
"OTD/ROV+2446.0000+ 0.0000"
"G59WO+5322.0000+- 112.4570"
"G59WO+5323.0000+- 619.6670"
"G59WO+5324.0000+ 36.6190"
"TLMLV+5601.0000+ 0.0000"
"TLMLV+5602.0000+ 20.0000"
"TLMLV+5603.0000+ 0.0000"
"TLMLV+5604.0000+ 0.0000"
"TLMLV+5605.0000+ 0.0000"
"TLMLV+5606.0000+ 75.0000"
"TLMLV+5607.0000+ 175.0000"
|
|
|
|
|
I have written the query(Update,View,Delete) in three Stored Procedure such as SP_GetOfficeHourToday,SP_UpdateOfficeOpenCloseHours,SP_DeleteOfficeHour
How to write the multiple query( View,Update,Delete) in single store procedure using SqlServer2005.
USE [Myrefer]
GO
/****** Object: Table [dbo].[Office_Hours] Script Date: 04/21/2008 17:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Office_Hours](
[DayOfTheWeek] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OfficeOpenHours] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OfficeCloseHours] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
VIEW
*******
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_GetOfficeHourToday]
@today varchar(10)
as
begin
select DayOfTheWeek,OfficeOpenHours,OfficeCloseHours from Office_Hours where DayOfTheWeek=@today
end
UPDATE
********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_UpdateOfficeOpenCloseHours]
@DayOfTheWeek varchar(10),
@OfficeOpenHours char(4),
@OfficeCloseHours char(4)
as
begin
update Office_Hours set OfficeOpenHours=@OfficeOpenHours,OfficeCloseHours=@OfficeCloseHours where DayOfTheWeek=@DayOfTheWeek
end
DELETE
*******
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_DeleteOfficeHour]
@today varchar(10)
as
begin
Delete from Office_Hours
end
modified on Monday, April 21, 2008 7:52 AM
|
|
|
|
|
bruze wrote: How to write the multiple query(like View,Insert,Update,Delete) in single store procedure using SqlServer2005.
I'm not sure how to answer this because you just write multiple queries in the stored procedure. If it makes you feel better you can put a semi-colon between each query.
|
|
|
|
|
You can do something like:
Create Proc up_Maintain (@mode char(1))<br />
if @mode = 'a'<br />
begin<br />
....code to add<br />
return<br />
end<br />
if @code = 'd'<br />
begin<br />
....code to delete<br />
return<br />
end<br />
begin<br />
....code to update<br />
return<br />
end
However this is bad practice as the optimiser will optimise for the first time the proc is run. An accepatable alternative is to have a proc like this, but keep the add/delete/update procs and just call thme from the controlling proc, but you gain nothing.
As far as I can see there is nothing to be gained from merging the procs together, but there are potential performance issues, so they should be kept independantly.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank You For Your Response.
|
|
|
|
|
We can use this procedure in c# application?
|
|
|
|
|
I have return the code in c# application using individual store procedue.
How to write the application code using Multiple query with single stored procedure?
public DataSet GetDayHours(string strSingleDay)
{
//Get the Day,OpenHours and CloseHours from Database
string GetConnection = Connection.GetConnection();
SqlConnection strConnectionString = new SqlConnection();
strConnectionString.ConnectionString = GetConnection;
strConnectionString.Open();
SqlCommand cmdsearch = new SqlCommand("SP_SelectOfficeOpenCloseHours", strConnectionString);
cmdsearch.CommandType = CommandType.StoredProcedure;
cmdsearch.Parameters.Add(new SqlParameter("@DayOfTheWeek", strSingleDay));
SqlDataAdapter daAdapter = new SqlDataAdapter(cmdsearch);
daAdapter.Fill(dsGetDayHours);
}
|
|
|
|
|
So you don't want to have multple actions in a single stored prcoedure? Please clarify what you do want to achieve.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi
how to get list of all tables that exist in my sql server instance ?
i'm using this scrpit :
select * from sys.tables
but this command return only tables that exist in Current Database.
thanks
|
|
|
|
|
Check my reply to you on C# forum.
I Love T-SQL
|
|
|
|
|
I have a nasty little job where I have a CSV table with 50+ cols which will grow (1-2 per year)
The last 44+ cols are in sets of 3, where AttibuteName changes but the date fields do not.
AttibuteName, Start_Date, End_Date
Therefore the columns are not unique and their number is not stable.
At this point SSIS seems to have a serious issue, however this could be resolved if the import method created cols based on the cols in the source file. I'm quite happy to have the labels in the 1st data row and all data to be varchar as I can deal with this in TSQL but dynamically mapping the new columns is the issue.
Ideally I would like to drop the table(no problem)
Bulk copy into a new table based on the source file
Rename the destination file (no problem)
Process the datavia stored proc (no problem)
I have written an app which does exactly what I want but it is a manual thing and I really want to automate this. Having told the boss that writing ETL apps for each module is wrong, I need to clean this one up.
TIA
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Customer can enrol several operation.
Each operation can be enroled by many Customer.
Customer own Operations and not own operations are displaied.
And When Customer will use a operation ,check it that has enroled.
how to design the tables to display these relation ?
I want to know the best desgin.
|
|
|
|
|
Hi
This is nothing ADO.NET but SQL... (not quite sure if this is right forum). I am new to Sybase and today using sqsh (http://www.sqsh.org/[^]) not sure how to "clear screen".
Anyone familiar with this?
Thanks!!
devy
|
|
|
|
|
May be The title Of the Question Sounds Strange But I Will Explain What I mean ...
I'm Analysing Web Application And I am Very confused
The Problem Is That I didn't use T-sql in Complex Scenario so I don't Know If the T-Sql
Has the Ability To manipulate Date Like .Net Languages In Easy Way ??
Of course I Know That There are loops, conditions , interact With Variables , Builtin Methods
And Etc......
But Is it Easy To Develop , Update .........
Thanks  !
You have To Search About The Truth Of Your Life
Why Are you Here In Life ?
|
|
|
|
|
I've seen the best of both worlds. Stored procedures so complex, thousands of lines long and splattered with dynamic sql's and temp tables and numerous loops. And also BLL's having thousands of procedures. In the end, I guess the call is yours, if you're the sole developer. If you're in a team, you might want to leverage your team's skillset.
Developing Business Logic in T-SQL is not for the faint hearted, and is usually lengthier and more complex than a similar implementation in .NET. And T-SQL is pretty limited when it comes to inbuilt functions and constructs. Nowhere near to the .NET libraries, that's for sure. A traditionalist would put his BLL's in the application and use the backend just for DML's.
In the end, it's your call. If your Business Logic is very complex, stick to .NET, else if it's just a few checks and conditions, T-SQL would do just fine.
Alternately if your database server is much more powerful than your web server, you also have the option of using CLR Stored Procedures.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Please don't cross post. I guess this is where it belongs, as your question had nothing to do with VB.
SQL is really easy. Much more so than VB. But, it can get complex, it depends on how complex you need to get, to get what you need. Buy a book.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
hi all
in my table i had a colum contain dattime data the formating date
like this "13/12/2007 09:16:41 "
when i make a select statement by where clause and between condition
as
select * from MessageAccepted where AcceptedDateTime between '22/10/2007'and '23/10/2007'
SQL gives me this error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
is there is a way to convert the datetime datatype and how???
or how can i solve this problem
please i need your help
thanks for all
Kareem Elhosseny
|
|
|
|
|
Convert on DateTime datatype as:
select * from MessageAccepted where AcceptedDateTime between cast('22/10/2007' as DateTime) and Cast('23/10/2007' as DateTime)
I Love T-SQL
|
|
|
|
|
thank's for your effort but i use it before and SQL gives me the same error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Kareem Elhosseny
|
|
|
|
|
Post your data how they are stored in table, and is datatype of column DateTime or varchar?
I Love T-SQL
modified on Sunday, April 20, 2008 4:07 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
