Split Function - Sql Server 2005
In my table, column1 having a comma separated values.I want to display in rowwise.
for example:
column1
aa,ss,ff
output should be
aa
ss
ff
Pls. help me..I want to do thr query.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Split Function
Is there any split function in sql server 2005. I just want to pass a string to a stored procedure. it should split that string when it encounters a space. for ex, consider the string " the intel mother board" it should give "the,intel,mother,board" as result.
View Replies !
Split Function
Hi I have tried like this. but I cant execute my SP it ended up with errors. IF (@FirstLetters IS NOT NULL) BEGIN SET @FirstLetter = 'SELECT SplitValue FROM dbo.FnSplitString('+@FirstLetters+','',''))' print @FirstLetter END SELECT P.PUB_ID AS ''PubId'', P.PUB_TITLE AS ''PubTitle'' FROM HDS_PUBLICATION P INNER JOIN HN_IM_JOIN IM ON IM.PUB_UNID = P.PUB_UNID, HDS_CUSTOM C, WD_PUBLIC_SHELF S, HDS_TOPIC T WHERE P.PUB_UNID = C.PUB_UNID AND C.CUSTOM3 = ''False'' AND P.PUB_ID = S.PUB_ID AND P.PUB_UNID = T.PUB_UNID AND S.Audience = ''Public'' ' + @FilterByLang + ' ANDP.PUB_TITLE LIKE '+ @FirstLetter +'''%''' +' EXEC SP Any one please clear me Thanks, shakthiA
View Replies !
Split Function In A Transaction
I have some sql results coming out as a string when it is a bunch of numbers separated with a coma. (1,2,3). I need to insert this figures as separate numbers in separate rows into a table in a database. How do I split them and how do I input them. I tried using a loop and it works well in an asp page but I need to do that in sql. JG
View Replies !
Split Function With Cursor
Hi, I am trying to write a stored procedure that takes a comma separated letter. I have a split function that returns the splitted letters. I have select some values from the tables in the database where the title starts with each splitted letter. I thought I should use cursor that contains each letter and in the while loop i put my select statement with the conditions. Is this the correct way. Or are there any ideas for this? thanks, Regards, shakthi
View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly.
I dont have the SQL EXPRESS installed instead I have SQL Standard Edition. I have two SQL Server instances installed. 1- UserLT (this is sql 2000)2- UserLTSQL2005 (this is SQL 2005 named instance) But when i try to add a database to my VS website project I get the following error: Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925 I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default) and changed the "SQLEXPRESS" to "USERLTSQL2005". But I still get the same error message. Any ideas how i can resolve this issue?
View Replies !
Connection To SQL Server Files (*.mdf) Require SQL Server Express 2005 To Function Properly
hello, i've installed SQL server 2005 express and Visual web developper 2005 express. when i whant to create a database in VWD the following error occures: connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251 i searched the internet but can't find the solution, i already reinstalled my complete workstation but the problem stays. please help!
View Replies !
VS2005 Error: Connection To SQL Server Files (*.mdf) Requires SQL Server Express 2005 To Function Properly.
Good Evening All, I've serached this forum and Google'd for a resolution to this issue, to no avail. Here's the scenario: I'm running VS 2005 on Windows Media Center laptop and need to create ASP.net membership for my web application built using VB. I have SQL Server Developer installed with instance name MSSQLSERVER. Previously, I uninstalled SQL Express and installed Developer edition and can now open and utilize Management Studio. Now, when I try to create a new SQL Server database in my solution's App_Data directory, I receive the above error. I already changed instance name in VS2005 per Tools-Options-Database Tools-Data Connections to MSSQLSERVER. Could someone provide me with a list of procedures to ensure proper setup of VS2005 with SQL Server Developer Edition? Thanks much for your help.
View Replies !
Sql Server 2005 GETDATE() Function
I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector. When I use the SQL Mangment Studio Express to execute this query: SELECT GETDATE() it returns 2008-04-22 16:25:03.690 which is OK !! I want to get that same value using a Java program so I do that query but when I display it it shows as: 2008-04-22 and there is no time there ! I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM So maybe the time isn´t there ? This is the code i am using: Statement st = con.createStatement(); String sql = "SELECT GETDATE()"; ResultSet result1 = st.executeQuery(sql); result1.next(); Date today = result1.getDate(1); Any help would be appreciated.
View Replies !
SQL Server 2005 Query With Date Function
I wrote a function and a SQL to get the 3 columns Date,Total Orders & Amount, for dates between Date Started and Date Completed if I pass different Dates in the SQL I get the correct result but if I pass same dates then I don't get the result I am looking for .For Instance,if I give Date From=1/02/2008 ;Date To=1/8/2008(Different dates )I am getting values for all the three columns.But I give same dates for Date From=01/02/2008 ;Date To=01/02/2008 then I am not getting the records.Some how I could not trace what could be the error in my SQL /Function.I appreciate if I could get some work around for this.Thanks! Function:create function dbo.CreateDateList(@start datetime, @end datetime)returns @t table ( [date] datetime )asbegin if @start is null or @end is null return if @start > @end return set @start = convert(datetime, convert(varchar(10), @start, 120), 120) set @end = convert(datetime, convert(varchar(10), @end, 120), 120) while @start < @end begin insert into @t ( [date] ) values (@start) set @start = dateadd(day, 1, @start) end returnend ---------SELECT qUERY---------- SELECT Convert(Varchar(15), l.[date],101)as Date,COUNT(o.OrderID ) AS TotalOrders,ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount , 1 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID GROUP BY Convert(Varchar(15), l.[date],101) Union SELECT 'Grand Total' as Total,NULL AS TotalOrders, ISNULL(Round(SUM(o.SubTotal),2),0) AS Amount, 2 as OrderByCol FROM dbo.CreateDateList(@DateFrom , @DateTo) l LEFT OUTER JOIN orders o ON o.Datecompleted >=Convert(Datetime, l.[date],101) and o.Datecompleted < dateadd(day,1,convert(Datetime, l.[date],101)) WHERE StoreID=@StoreID Order by Date
View Replies !
How To Run User Defined Function On Sql Server 2005
Dear all I wants to run sql server user defined function when linked two server. I have linked two sql server.There is one function called getenc().This function created on first server.What i want.I wants to run this user defined function on the second sql server. can any one help me? Regards Jerminxxx
View Replies !
SQL Server 2005 Query/trigger/function (whatever It Is That I Need)
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario table 1 = stock table table 2 = Purchase order table in table 2 there are line items that have ordered quantities for parts that we have ordered in table 1 under each part number is a field for "quantity on order" I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1 quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match so for part number 516 i have this table 2 poNumber partNumber quantity 1 516 1 2 516 12 3 516 4 table 1 partNumber inStock onOrder 516 0 17(this is what i am trying to figure out how to compute) any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.
View Replies !
Does SQL Server 2005 Has Similar Function Like Mysql_fetch_row ?
I just began to use SQL Server 2005 as database programming and found out that I have to translate mysql_fetch_row into SQL Server 2005 but I cannot find some related functions/api, and I was wondering Does SQL Server 2005 has similar function like mysql_fetch_row ? Or if not, any advice how I can program to acheive similar functions ? thank you in advance.
View Replies !
How To Create Assembly Function Using Dll Files In SQL Server 2005???
Hiiiiiiii all I have to make a user defined function in c# as the class liberary and create a dll file, now i want to use this function in SQL Server 2005 as a part of CLR Integration I have tried like this CREATE ASSEMBLY abc FROM 'C:abc.dll' WITH PERMISSION_SET = SAFE but it gives me incorrect syntax error so plzzzzz anyone help me wht to do in my probbbbbbbbb??????? Pratik Kansara
View Replies !
SQL Server 2005 SELECT MAX Function For Multiple Columns On The Same Record
Hello, I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column. Any help you can provide will be greatly appreciated! Thank you, Nathan
View Replies !
ODBC Link From Access To SQL Server 2005 Stored Function
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC? I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access. I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time. What am I missing? Suggestions?
View Replies !
Problem With (SQL Server 2005) Reporting Services Drill Through Using SWITCH Function In Sharepoint Integration Mode
When deploying reports to a report server in Sharepoint Integration mode (post SQL 2005 SP2), when using the SWITCH function when drilling through to another report ('jump to report'), you have to append the ".rdl" entension to the report name. For example, on a report server in native mode, the following will work: =SWITCH(Fields!Region.Value="US","USRegionDrillDownReport", Fields!Region.Value="Europe","EURegionDrillDownReport") When deploying to a report server in Sharepoint Integration mode, you must do the following: =SWITCH(Fields!Region.Value="US","USRegionDrillDownReport.rdl", Fields!Region.Value="Europe","EURegionDrillDownReport.rdl") When doing this, the deployed report will work, but the link in the Visual Studio developer environment will no longer work (and vice versa).
View Replies !
Trying To Split My Columns Into Years (col1 Must Have Vals For 2005, Col2 Vals For 2006)
Hi, i'm reasonably new to reporting services and am looking for a way to split my reports' Years to compare the months in year 2005 to 2006 but i can't get my data nest to one another in a single line, it splits the years into different rows as an example this is what i want if you can decipher that 2005 2006 Growth 2005 Year to Date 2006 Year to Date Year to Date Growth turnover gross profit turnover gross profit turnover gross profit turnover gross profit Jan 250500 75300 280200 84100 11.85629 11.686587 250500 75300 Feb 205000 67950 190350 59900 -7.14634 -11.84695 455500 143250 take the month above and add the current months values Mar 217670 70540 234200 78000 7.594064 10.57556 673170 213790 Apr 270780 84000 290400 93000 7.245735 10.714286 943950 297790 May 265000 79260 289050 90200 9.075472 13.802675 1208950 377050 Jun 277300 81050 277900 82000 0.216372 1.172116 1486250 458100 Jul Aug Sep Oct Nov Dec Here is my Query: SELECT /*DT.[YEAR],*DT.[MONTH],*DT.MONTH_NAME,*/ DC.CLIENT_KEY, (select SUM(FT.Cost)where dt.[year] = 2005) AS COST , (select SUM(FT.Price)where dt.[year] = 2005)AS SALES,(select SUM(FT.Cost) where dt.[year] = 2006),(select SUM(FT.Price) where dt.[year] = 2006)--, SUM(FT.QTY) AS QUANTITY, SUM(FT.PRICE) - SUM(FT.COST) AS GP,(SUM(FT.PRICE) - SUM(FT.COST)) / SUM(FT.PRICE) * 100 AS GP_PERCENTAGEFROM FACT_TRANSACTION FT, DIM_TIME DT, DIM_CLIENT DC, DIM_INVOICE_TYPE DIT, DIM_PRODUCT DPWHERE FT.TIME_KEY = DT.TIME_KEYAND FT.PRODUCT_KEY = DP.PRODUCT_KEYAND FT.CLIENT_KEY = DC.CLIENT_KEYAND FT.TYPE_KEY = DIT.TYPE_KEY AND DIT.TYPE_KEY NOT IN (5,6,13,14,15,16,17)AND DC.CLIENT_SERIALNO = '86634'--AND DT.[YEAR] IN(2005,2006)AND DT.[MONTH] IN(1,2,3,4,5,6,7,8,9,10,11,12)AND DP.PRODUCT_KEY <> 1668684GROUP BY DT.[YEAR],DC.CLIENT_KEY--, DT.[MONTH]ORDER BY /*DT.[YEAR],*/DT.[MONTH] but it returns everything under one another 2005 1 January 2005 3 296092.3431 405263.62 12811 109171.2769 26.93 2005 2 February 2005 3 318597.658 432098.17 13220 113500.512 26.26 2005 3 March 2005 3 371327.721 506481.46 15283 135153.739 26.68 2005 4 April 2005 3 371647.994 504713.99 15491 133065.996 26.36 2005 5 May 2005 3 400870.6138 542759.57 16296 141888.9562 26.14 2005 6 June 2005 3 399673.0086 546110.59 16607 146437.5814 26.81 2005 7 July 2005 3 390477.7521 535531.40 16153 145053.6479 27.08 2005 8 August 2005 3 380628.57 520281.87 15800 139653.30 26.84 2005 9 September 2005 3 340949.8849 471861.17 14820 130911.2851 27.74 2005 10 October 2005 3 340240.804 470007.78 14444 129766.976 27.60 2005 11 November 2005 3 349156.1871 481193.61 14523 132037.4229 27.43 2005 12 December 2005 3 346038.5059 477011.72 14865 130973.2141 27.45 2006 1 January 2006 3 340062.1369 470010.08 14037 129947.9431 27.64 2006 2 February 2006 3 328463.9689 452404.79 13996 123940.8211 27.39 2006 3 March 2006 3 375264.977 517800.27 16065 142535.293 27.52 2006 4 April 2006 3 412708.965 567014.52 17550 154305.555 27.21 2006 5 May 2006 3 446973.4231 606476.26 18920 159502.8369 26.29 2006 6 June 2006 3 406072.4943 544634.77 17053 138562.2757 25.44 2006 7 July 2006 3 389104.6316 526091.14 16228 136986.5084 26.03 2006 8 August 2006 3 317810.4531 431530.58 13641 113720.1269 26.35 2006 10 October 2006 3 405230.7083 549310.72 17151 144080.0117 26.22 2006 11 November 2006 3 379788.6645 514554.14 15917 134765.4755 26.19 2006 12 December 2006 3 393235.0906 531582.69 16924 138347.5994 26.02 If i do get them split then it put every year's value on a different line 2005 1234123.34 32432432.43 NULL NULL 2006 NULL NULL 12312.212 15235453.21 Please Help,
View Replies !
SQL Server 2005 &&"lag&&" Function
Hello, Does SQL Server 2005 have anything like a "lag" function that, in a sigle query allow you to retrive a previous record? The syntax would be like: SELECT transactionid ,transactiondatetime ,amount ,amount + LAG(amount) OVER(ORDER BY transactiondatetime) balance FROM bankaccount Thanks for any advice
View Replies !
No TRIM Function In 2005 - Are You Serious!?
I cannot believe that there is yet another version of SQL Server without a TRIM function. So for SQL 2005 (like I've done in 7.0 and 2000) my T-SQL will look like dog poo because I will have to write LTRIM(RTRIM(MyValue)) anytime I want to remove whitespace from both ends of character values instead of just writing TRIM(MyValue).
View Replies !
Can A Sql 2005 Function Return More Than A Variable
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance
View Replies !
Calling VB.NET Function From Trigger In SQL 2005
I have VS 2003 & SQL Server 2005.I have created VB.NET console application which calls various function. Based on data insertion/ updatation in SQL 2005 I need to call function from my VB.NET application. That is from SQL insert/update trigger I need to call function from my console application which is continuouly running. I need help on how can I capture insert trigger event VS 2003 console application?
View Replies !
Fail To Create CLR Function In SQL 2005
Can anyone help me to create a URL decode user defined function in SQL Server 2005? I want to use the method [System.Web.HttpUtility.UrlDecode] in .net framework, and I try to add it as a CLR function to SQL Server but always fail. It depends on [System.Web.dll], and when I try to create assembly [System.Web] using following scripts, it will fail: CREATE ASSEMBLY [System.Web] FROM 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll' WITH PERMISSION_SET = UNSAFE Error: CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Web' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. I have searched the MSDN and then add following scripts before mine: ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON CREATE ASYMMETRIC KEY SystemWebKey FROM EXECUTABLE FILE = 'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll' CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY SystemWebKey GRANT UNSAFE ASSEMBLY TO CLRLogin But unfortunately it fails again with same error.
View Replies !
2005: Creating Aggregate Function From .NET Assembly
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View Replies !
Create A TO_DATE Function For Use In SQLServer 2005
Hi , I 'm working with visual studio 2005 and I have created an SQLServer Project. I'm using the CLR functionality which comes with SQLserver 2005. This means that I can write VB.nEt code and use it inside Sqlserver 2005.So far so good. I am now inside the .NET I have created a Function(must remind you that I have created an SQLserver Project) which takes two string arguments. The date value in a string format and the string format. In Our case the function returns a string.It will return a datetime although. So we have Dim Datetime_Val As DateTime = Nothing Dim Date_Val As Date = Nothing Dim StrTemp As String = "" Dim StrDateTemp As String = Nothing Dim StrTimeTemp As String = Nothing Dim ls_return As String = Nothing Dim lindexof As Integer Dim Counter As Integer = 0 lindexof = 0 Select Case StrFormat Case "DD-MM-YYYY HH24:MIS" For Counter = 1 To 2 lindexof = StrDate.IndexOf("-", lindexof + 1) Next lindexof += 5 StrDateTemp = StrDate.Substring(0, lindexof).Trim StrTimeTemp = StrDate.Substring(StrDateTemp.Length, StrDate.Length - StrDateTemp.Length).Trim ls_return = StrDateTemp & " " & StrTimeTemp End Select The above is a simple code. As you can see I'm trying to convert the TO_DATE function ,which work with ORACLE, to make it work with SQLServer 2005. I've been trying unsuccessfully to combine the variables StrDateTemp and StrTimeTemp into a datetime value. I used the following code but nothing Datetime_Val = CDate(StrDateTemp & " " & StrTimeTemp) Didn't work Datetime_Val = Convert.ToDateTime(StrDateTemp & " " & StrTimeTemp) Didn't work Datetime_Val = DateTime.Parse(StrDateTemp & " " & StrTimeTemp) Didn't work Inside SQlServer I used this SQL statement Select dbo.TO_DATE('31-12-1990 00:26:46','DD-MM-YYYY HH24:MIS') But I am receiveing an error. I want to avoid changing all of my applications with a specific format.This sql statement without the dbo prefix I'm using in Oracle. I want to keep the format of the SQL and let VB.NET do the parsing for me. It is easier for me to put in my SQLs the dbo infront rather changing the complete SQL. I have two questions . How am I going to create a TO_DATE function which Oracle uses and write something similar in SQLserver ? And If I cannot do that how am I going to get the database 's datetime format and create with VB.NET the Datetime value from the two variables ? My problem I believe is quite complex. I would be mostly appreciated if you could help me on this. Thank you
View Replies !
Sql 2005 ... Invalid Length Parameter Passed To The SUBSTRING Function.
Hi, I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error, €œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ Through research I have no clue as what I need to do, or where to look. The package runs without error when I execute the package itself. Any help is greatly appreciated. Thanks, Lori
View Replies !
'Copy Website' Function In VS 2005 Updates Or Overwrites Database?
I have a sql 2005 express database uploaded to my website with important information in it. Now, I had to make some table change and need to update the online database. I am not sure if the 'Copy Website' function in Visual Studio 2005 will update the database structure and data or will simply overwrite it. Does anybody know the answer? If it overwrites it, would you please point me to information on how can I update the database structure and data without ruining it? Thanks.
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Name Split
Quick question. I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it). Thanks in advance. Ray
View Replies !
Name Split
I need to split a column of Full Names into First name and Last name columns. Has someone come across this before and if so can you give me an idea of how to overcome it?
View Replies !
Split Value In Sql
Hi to all I have one problem regarding sp and pass value in sp I am gating a value like Abc,Def,Ghi, Now I want to split the whole pass value by “,� And fire one for loop to store value in database This things is done in asp.net web form but I want to do all process in sp So please guide me how I am write sp . The purpose is pass value one time so connection time is decrees and give fast perforce
View Replies !
To Split Or Not To Split
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!! Is 8,000 or so rows of data "large" and unwieldly in SQL terms? Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about............. What did they say about a little bit of knowledge being a dangerous thing? Thanks in advance of any guidance to a neophyte!!?
View Replies !
SPLIT() UDF
SQL UDF split() The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string. Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure. To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results. If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters. Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table. Here is how I handled the above situation. 1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max)) 2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string 3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data The basic structure of the stored procedure is as pasted below:- CREATE PROCEDURE FETCH_SALES_DETAIL ( @MONTH VARCHAR(MAX) ) AS BEGIN DECLARE @MONTH_CNT INT,@MTH DATETIME SET @MONTH_CNT=1 WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> '' BEGIN SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME) --<<Application specific T-SQLs>>-- (BEGIN) SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE] FROM SALES (NLOCK) WHERE [SALES_MONTH]= @MTH --<<Application specific T-SQLs>>--(END) SET @MONTH_CNT=@MONTH_CNT+1 END END Dbo.SPLIT() function takes 3 parameters 1)The main string with the values to be split 2)The delimiter 3)The Nth occurrence of the string to be returned The functionality of the UDF is as explained STEP by STEP: 1.Function Declaration CREATE FUNCTION [dbo].[SPLIT] ( @nstring VARCHAR(MAX), @deliminator nvarchar(10), @index int ) RETURNS VARCHAR(MAX) Function is declared with 3 input parameters:- @nstring of type VARCHAR(MAX) will hold the main string to be split @deliminator of type NVARCHAR(10) will hold the delimiter @index of type INT will hold the index of the string to be returned 2.Variable Declaration DECLARE @position int DECLARE @ustr VARCHAR(MAX) DECLARE @pcnt int Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter. 3.Variable initialization SET @position = 1 SET @pcnt = 1 SELECT @ustr = '' Initialize the variables 4.Main functionality WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index BEGIN IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN IF @pcnt = @index BEGIN SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar) END SET @position = @position + 1 END ELSE BEGIN SET @position = @position + 1 SET @pcnt = @pcnt + 1 END END 4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter. 4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter 4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented. 4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable 5.Return the value RETURN @ustr I hope this article would benefit those who are looking for a handy function to deal with Strings. Feel free to send your feedback at dearhari@gmail.com
View Replies !
|
TRACKER
