How To Split One Coloumn Into Two Coloumns In Sql Server 2000
I have one table 'tbUser' having coloumns
UserID,
UserName,
Category
UserID UserName Category
1 Shailaja H
2 Anagha H
3 Raju L
4 Kiran L
I want output as
UserID UserName HigherCategory LowerCategory
1 Shailaja H
2 Anagha H
3 Raju L
4 Kiran L
View Complete Forum Thread with Replies
Related Forum Messages:
How To Join Two Tables Having No Common Coloumns And Having No Primary Key Fields In Any Of The Table
hi friends i am reading a csv file and storing the data in table i have created the tables like this: coloumn names datatype constraints start_date_time varchar(50) allownull = true ani varchar(50) allownull=true dialed_digit varchar(50) allownull=true actual_dur varchar(50) allownull=true rounded_dur varchar(50) allownull=true cost varchar(50) allownull=true and the second table as coloumn names datatype constraints country varchar(50) allownull = true code varchar(50) allownull=true rate/min varchar(50) allownull=true now i want to relate and join the table so that i can create a view with the following coloumns such as start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
View Replies !
To Order More Than One Coloumn
Hellos; I have a lot of News. Only i want to get last 10 news and piorty is important for me to change orders of the news. So i add a new coloumn in the table (Piorty int) ... select top 10 NewsID, Piorty, Title, Spot from News order by 1 desc, 2 asc -- i want to get last 10 news than order by piorty (which piorty is bigger is be thre first) but in this query i just get last 10 news desc the piorty isn't imports even though i would get them order by piorty but last 10 news... How can i do that ??? Please hellppp ... thnank you verry muchhh ...
View Replies !
Store All Data In One Coloumn ?
we need to decide an architecture for Performance on a web site Search! I wanna use text service of SQL 2005 .But I am worried about the performance .... How should I design the system if I want the best perfomance and scalability ?1.Should I build a seperate coloumn in my every table and merge all the information into one coloumn and full text index that column.2.Put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.3.Make a different table and put _ID,_TYPE and _VALUE fields and search in that table with less coloumns.4.Seperate the full text database and search in a seperate db so that I can scale better?did anybody have a similiar problem ? Any books on full text search ?
View Replies !
Comma Deliminited Coloumn
I have two tables. One table (Table 1) has a column containing commadelaminated email addresses. The other table (Table 2) has a columnwith just one email address in it. I need to perform a query thatjoins the comma delaminated table (Table 1) to Table 2, when the singleemail address in Table 2, is contained in the list of email addressesin (Table 1).I hope this isn't too cryptic, and I know comma delaminated lists arebad, but I can't do anything about that.I need a select statement that can perform this task. Anyone have anysuggestions?
View Replies !
How To Split Data From The MS SQL 2000 Database?
Hi All, We are working on a project, (C#.Net 2003, MS SQL 2000) where database is growing as 2 to 3 GB per day (Scanned Documents are storing in Database). There is only one table in the database. Currently database size is grown upto 200 GB. For safety reason we are planning to split the database accoring to one key field in the table say Book_no. Please guide/suggest me how to split the database now on the SQL query like "SELECT * FROM master_records WHERE book_no=1" And later how to merge/combine all these splitted database into one. I thought of using "Data Export" and later "Import with Append" but don't know whethere it will effective or not? Any method or tool available to Split the database table and later combine them to make again a single one? Please help me, its urgent. Thanks in advance.
View Replies !
How To Join Two Tables Having No Common Coloumn
hi friends i am new to this forum and to the tech. also , i am reading a csv file and storing the data to the table of sql server 2005 database but the problem is i cannot join both the tables because both the tables doesnot have common cols and ther is no primary key field in any of the coloumn i have tried the sql query but i didn't got the desired output so could anyone help me please see the sql query of mine is like this: SELECT dbo.table1.country, dbo.table1.code, dbo.table1.rate_min, dbo.table2.start_date_time, dbo.table2.rounded_dur, dbo.table2.cost, dbo.table2.dialed_digits FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 2) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 3) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 4) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 5) OR dbo.table1.code = SUBSTRING(dbo.table2.dialed_digits, 1, 6) please help me its urgent
View Replies !
How To Dynamically Map The Coloumn To A Flat File Destination?
Hi All, I am struck at one point. I am trying to this operation and not able to go further. 1. I have got the dataset to a variable in the control flow. 2. I am looping through the dataset based on a coloumn. 3. Now inside my For each loop i have a dataflow task. 4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @othervariable. Now my question is Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns. Thanks in advance.. Regards, Dev.
View Replies !
Making The Value Of One Coloumn The Same For A Large Set Of Data In A Table?
Hello, Im a very new to SQL server etc, so please bear with me. I am currently trying to find a quick way of making a large number of database entries within a particular table all have the same value in one particular column. I have created a query in MS SQL Server Management Studio, which outputs all the entries in a particular table that I want to change. Currently they all have different values in a certain column in this table, and I want them all to have the same value in that column. How do I do this within Server Management Studio? Thank you.
View Replies !
Not Able To Install SSRS For SQL Server 2000 On Production Machine (Windows 2000 Advanced Server)
Hi, I am not able to install SSRS for SQL Server 2000 on our production server, which is Windows 2000 advanced server and it is giving error messages in the last minute and before completing the set up and the message is displayed in meesage box as "SQL Setup failed to execute a command for Server configuration. The error was: Line 1: Incorrect Syntax error near '[]'. Refer to Server logs and set up logs for detailed error information " Please help on this at the earliest. Appreciate your help in advance.
View Replies !
Upgrade - Server 2000/sql Server 2000 To Server 2003/ Sql Server 2005
I am converting about 50 databases from Windows 2000 and from SQL Server 2000 to a totally new 4 processor Server 2003 with SQL SErver 2005. I used the Database Copy Wizard in SS2005 to do the transfer and I used the object method. After making sure the databases were big enough to hold the new Masterdatabase everything went well... except none of the stored procedures from any of the 50 databases copied from SQL Server 2000 to SQL Server 2005. What did I do wrong? And how do i fix it. Also, even though I changed the default database, the wizard did not reflect the new SAN location for the Data and Log files. I had to do each one manually.
View Replies !
Transition From Sql Server 2000 Developer To Sql Server 2000 Standard Edition
I have installed MS sql server developer edition on a server, created database and tested it and it all work fine. I have thousands of records and lots of store procedures to apply business rules. Since the developer edition has limitation in deployment into the production environment. I have obtained a new MS sql server Standard Edition so I can deploy my database in a production server (which is the same server I installed the developer edition)...... What are the best way to install the standard edition. options: 1- Back up the database , uninstall developer edition from server, then install the standard edition, then restore the database into the standard edition 2- install the standard edition as a second instance in the same server where the developer edition is installed, then move the complete database. 3- have a complete script copy of the database structure, store procedure, views,etc.. then bcp out all current data, then uninstall developer edition, then install standard edition, then run the scripts to build the data base, then bcp in the data. You can tell that I really do not know what is the best safe way to accomplish this task, appreciate your input in this one. Thanks Ali
View Replies !
SQL SERVER 2000: In Which Format The Datetime Will Be Stored In Sql Server 2000?
Hi All, I would like to know, how the datetime will be stored in the sqlserver datetime column. Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy. while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in yyyy/mm/dd format. So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database? Thanks in Advance. Regards, Dhanasekaran. G
View Replies !
Sql Server 2000 Linked Server To Access 97/2000 Incompatability
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View Replies !
Upgrading SQL Server 2000 Standard To SQL Server 2000 Enterprise
I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.
View Replies !
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
HI, I installed Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A in my computer, but I can't see MSDE on my Start menu. I am really new to this and don't know how to open MSDE, so I could work with it. Sorry for such a silly question, but please help me. How can I open MSDE from the Start menu. I have windows XP professional. Thanks for the help. Munny
View Replies !
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A
My objective is to use Enterprise Manager to move (copy) my SQL db from the server to my windows desktop computer. I downloaded MSDE and am having trouble installing it, no doubt because I do not understand the documentaion (ReadMeMSDE2000A.htm). When I try to run setup, I get that message that says: "A strong SA password is required for security reasons. Please use SAPWD switch to supply the same." Considering my purpose, do I need a "strong" SA password? If not, how do I get around it? If yes, how do I set it up? I am a Mac user so I have poor windows skills, please make it as painless as possible for me, thanks! Ron
View Replies !
Vista Business ODBC/DSN To Remote Windows 2000 Server / SQL 2000
Hi, Just upgraded some development desktops to Vista Business. However we need to still connect to some older remote windows 2000/SQL 2000 servers. Trying to setup an ODBC system DSN on our Vista Business local desktop we get the following errors - -START ERROR WINDOW- Connection Failed: SQLState: '01000' SQL Server Error: 772 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (SECDoClientHandshake()0. Connection failed: SQLState: '08001' SQL Server Error: 18 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error -END ERROR WINDOW- Any help greatly appreciated as this is stopping us from making database/table connections etc. We've checked the firewall setup and all is well there. PS - we can still connect fine using XP or windows 2000 desktops and their local DSNs.
View Replies !
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release
venu writes "Hi, Am very new to MS SQL adminstration Can anybody help me out how to work on Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Release A just for the practice. The activity which am going to workout on MSDE is below. How to install SQL(on XP) How the layout will be(like if i insall MSDE what are all Application will be and how they depends on each other) How to create/delete tables if so, how can we do it either by GUI or CUI just i need a clarifications reg same Thank you, venu"
View Replies !
Maximum Capacity Specifications Comparison Table For Access, SQL Server 7, 2000 And MSDE 2000
Parameter Access 2000/XP SQL Server 7.0 SQL Server 2000 MSDE 2000 Number of instances per server n/a n/a 16 16 Number of databases per instance / server n/a 32,767 32,767 32,767 Number of objects per database 32,768 2,147,483,647 2,147,483,647 2,147,483,647 Number of users per database n/a 16,379 16,379 16,379 Number of roles per database n/a 16,367 16,367 16,367 Overall size of database (excluding logs) 2 GB 1,048,516 TB 1,048,516 TB 2 GB Number of columns per table 255 1024 1024 1024 Number of rows per table limited by storage limited by storage limited by storage limited by storage Number of bytes per row (Excluding TEXT/MEMO/IMAGE/OLE) 2 KB 8 KB 8 KB 8 KB Number of columns per query 255 4,096 4,096 4,096 Number of tables per query 32 256 256 256 Size of procedure / query 64 KB 250 MB 250 MB 250 MB Number of input params per procedure / query 199 1,024 2,100 2,100 Size of SQL statement / batch 64 KB 64 KB 64 KB 64 KB Depth of subquery nesting 50 32 32 32 Number of indexes per table 32 250 (1 clustered) 250 (1 clustered) 250 (1 clustered) Number of columns per index 10 16 16 16 Number of characters per object name 64 128 128 128 Number of concurrent user connections 255 32,767 32,767 5
View Replies !
Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View Replies !
Problem Using Access 2000 As A Front-end To SQL Server 2000 Tables
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View Replies !
SQL Server 2000 Installation Problem On Windows 2000 Professional
Hello,I received the error message below when i'm trying to install SQLServer 2000 standard edition into a Windows 2000 Professionaleworkstation.Error :Microsoft SQL server 2000 Standard Edition server components is notsupported on this operating system. Only client components will beavailable for installation.Any request modification ?Best regards,Thanks
View Replies !
Problem By Design - MS-SQL Server 2000 And MS-Access 2000 Project.
Hi.Sorry for cross posting!After I've installed MS-SQL Server 2000, I've got a problem each time I opena table, view or diagram in design, in a MS-Access project.Something like a table is popping up, but only the headline of the table,there is no name in the headline. The (ghost) table is automatic gettingfocus, (like a dialog box), and I can't do anything.I can close the (ghost) table using ALT-F4 combination, but it is veryannoying me.I've tried to uninstall both the MS-SQL Server 2000 and MS-Access 2000, I'vealso tried to compact and repair the database without luck.If I connect to MS-SQL Server 7 on another computer, (same database),everything is fine.My OS is Windows XP Home edition; the MS-SQL Server 7 is running on Windows98.Does anyone here have had the same experiences?TIAJørn
View Replies !
Windows 2000 Sp4 && SQL Server 2000 Causes Java Core Dump
I've just started getting this EXCEPTION_ACCESS_VIOLATION (0xc0000005) on machines using Windows 2000 sp4 connecting to SQLServer. This is crashing JVMs (multiple Sun versions and BEA also) in the Java VM frame (outside our code). This has just started recently - perhaps with the last set of patches? Has anyone else seen this or know what I could do to get more information? Could this be related to updates to named pipes? Thanks! -Brian Temple
View Replies !
Converting Data From Access 2000 To SQL Server 2000
Hi, I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great. Thanks
View Replies !
Importing Data From Pervasive 2000 To SQL Server 2000
I am working on bringing data into our SQL Server from a btreivedatabase through Pervasive.It seems to work, mostly, but I am having problems with dates.At first, all date conversions from btreive field type Date to SQLServer SmallDateTime would produce nothing but errors of this sort:ActiveX Scripting Transform 'DTSTransformation_1' encountered andinvalid data value for 'HomesteadDate' source column.Looking into the data on the pervasive side, many of the the datefields had no data in them.I tried to get around this by changing all lines that brought overdates from this:DTSDestination("HomesteadDate") = DTSSource("HomesteadDate")to this:IF ISDATE(DTSSource("HomesteadDate")) = 1 THENDTSDestination("HomesteadDate") = DTSSource("HomesteadDate") ELSEDTSDestination("HomesteadDate") = NULLWhat I have discovered since then, is that all my dates are simplyfailing the ISDATE check and returning null to the SQL Server, evenwhen it is a valid date.Any suggestions would be greatly appreciated.Steve HoyerGPW, MI
View Replies !
SQL Server 2000 - Problems When Installing Windows 2000 SP4?
I installed Windows 2000 SP4 on my server this evening. We ran intoan odd error, and (1) wanted to share, and (2) wanted to see if therewere any other things I should be on the lookout for.The problem we ran into this evening was "no storage was available".Email wouldn't work, Openqueries to other systems didn't work, and Icouldn't log in except as SA. Solution: upgrade IE to 128bitencryption. Since it's a server, I've never bothered upgrading theWeb browser on the system, but apparently SP4 does do something withthe encryption that breaks things. CryptoApi errors.We upgraded IE and it seems fine now, but I wanted to see if there wasanything else like this I should be on the lookout for. Thanks.
View Replies !
Access 2000 On Windows 2000 Can't Interact With SQL Server 7.0
We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new server which is Windows 2000 based and the domain is different from the SQL servers domain. We then installed Access 2000 on Windows 2000 to use with terminal server. But I noticed that there was a problem with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem. In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.
View Replies !
Sql 2000 Installation On Windows 2000 Advance Server
I am trying to install microsoft sql server 2000 enterprise edition on a windows 2000 advance server. I keep getting error message A previous program installation created pending file operations on the installation machine. You must restart the computer before running . I have restarted machine and no change. What does error message mean and what is solution?
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
