Splitting FullName In A SQL String..
I use to use the Instr() method, but that is not allowed in SQL..
Any suggestions?
View Complete Forum Thread with Replies
Related Forum Messages:
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Hello All, I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database. There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example: 2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1 The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed. How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table? In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND(). Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion. Thanks a bunch in advance, Chutikorn
View Replies !
Splitting A String
Hello,I have been placed in charge of migrating an old access based databaseover to sql server 7.0. So far, I have imported all the tables intosql server, but now I have come across the issue of needing to split astring variable. For instance, in the old database, the variable forname was such that it included both first and last names, whereas inthe new database there are seperate entities for first and last name.I know that there is a way to write a script that will separate out thetwo strings by using the "space" in between the name, but I'munfamiliar how to do this. Any suggestions? Thanks!Rick
View Replies !
Splitting A Sql String
aaaaa,bbbb,ccccc,dddd,ffff,gggg,llll,kkkk,nnnnn How can split the above string to the following individual strings aaaaa bbbb ccccc dddd ffff gggg llll kkkk nnnnn Thanks
View Replies !
Splitting Up A String
If I have a string of 'WLL EXT FACE REND' how would I go about splitting the string into 4 strings of 'WLL' 'EXT' 'FACE' 'REND' I know that for the first bit I can use a combination of PATINDEX and LEFT, but not sure how I can pull out the rest of them. Thanks
View Replies !
Splitting Up One String Into Three Columns
I'm trying to split a hyphen-delimited string into three columns in a view. I've been using substring and len to split up the string, but it is getting very complicated (and isn't working in all cases). I've used a SPLIT function in vbscript - does t-sql have anything similar? I've attached a spreadsheet that shows what I am looking for. Maybe someone can guide me in the right direction? Thanks.
View Replies !
Splitting A String By The '|' Character
I need to creat distinct terms of the example parsing the term on the '|' character. I will be using mysql. example: 1885-1974.|Johnson family|Frontier and pioneer life - Alberta - Black Hill district|Cadogan region (Alta.) - Biography|Black Hill district (Alta.) - Biography
View Replies !
Splitting A Comma Delimited String
I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be 3:4:5:4 2:4:1 4:1:2:5:2 I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows: Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255) Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2' While @loc != 0 begin set @prevloc=(case when @loc = 1 then 0 else @loc end) +1 set @loc = charindex(',',@person,@loc+1) Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end)) print 'String = ' + @Str set @cnt=@cnt+1 end RESULT ------ String = 3:4:5:4 String = 2:4:1 String = 4:1:2:5:2 Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.
View Replies !
ORDER BY Question: Splitting String Into 2 Orders?
I have a column named "LIST" in a table with strings like the following:151231-1002-02-1001151231-1001-02-1001151231-1002-02-1002151231-1003-02-1001etc....What I'd like to do is include an ORDER BY statement that splits thestring, so that the order would be by the second set of four numbers(i.e. between the first and second - marks), followed by the third setof two numbers, and then by the last set of four numbers.How would I do something like this?--Sugapablo - Join Bytes!http://www.sugapablo.com | ICQ: 902845
View Replies !
Splitting Space Delimited String Inline
So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask). Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this? SELECT forenames , Replace(forenames, ' ', '.') , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3] , Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4] FROM ( SELECT 'John' As [forenames] UNION SELECT 'John Paul' UNION SELECT 'John Paul George' UNION SELECT 'John Paul George Ringo' ) As [x] Results forenames (no column name) f1 f2 f3 f4 ---------------------- ---------------------- ---- ---- ------ ----- John John John NULLNULL NULL John Paul John.Paul John PaulNULL NULL John Paul George John.Paul.George John PaulGeorgeNULL John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo
View Replies !
Splitting Text String Into Multiple Columns
I'm creating a web-based NT RAS report site and am looking for the most efficient way to import the data from NT Event log into SQL2k. I'm using the 'dumpel' utility from rsc kit and all is fine except the 10th column - the message detail: "The user DOMAINuserid connected on port Mdm15 on 08/23/2002 at 07:25am and disconnected on 08/23/2002 at 07:27am. The user was active for 2 minutes 23 seconds. 78809 bytes were sent and 50675 bytes were received. The port speed was 49300." I need to parse this one long text string into 6 distinct columns: userID, port, duration, bytes_xmt, bytes_rcv and portspeed. After a quick review of the rowsets, the strings seem to hold a consistent output ... no real variances I can see. I've dablled with views but am facing a small performance issue that could get bigger: The sql server not only has to run the text file import package, but also the view to format the text dump into a workable dataset, then my report code bangs over 30 queries against the final dataset. It already takes our SQL2k server over 3 minutes to parse about 20,000 rows and the server's a beast (dual 1.8 p4 cpu, 3gb ram, raid, etc). What I think would work best is to abandon the view (performance will only get worse as the row count increases) and instead INSERT the rows into one table. Any ideas anyone? any good scripts out there that can help me to parse the long text string quicker that using substring and replace functions? TIA:rolleyes: :rolleyes:
View Replies !
URGENT!!! Search Tool Function: Splitting A String
Hi All!!! I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem: The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned. So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character. Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID 2 FROM CRPL 3 WHERE (Trans LIKE '%' + @Trans + '%') OR 4 (Name LIKE '%' + @Name + '%') OR 5 (Address1 LIKE '%' + @Address1 + '%') OR 6 (Address1T LIKE '%' + @Address1T + '%') OR 7 (Address2 LIKE '%' + @Address2 + '%') OR 8 (Address2T LIKE '%' + @Address2T + '%') OR 9 (City LIKE '%' + @City + '%') OR 10 (CityT LIKE '%' + @CityT + '%') Thanks for all your help in advance!!!
View Replies !
Help Required For Splitting Up String Variable Using Comma Separator
I need a help in SQL Server 2000. I am having a string variable in the format like -- (1,23,445,5,12) I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop. Can anyone help me out in splitting the variable using the comma separator...
View Replies !
Opinion About Design Needed (splitting String Data)
Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik
View Replies !
Splitting A String By A Delimiter, But Ignoring Multiple Delimiters
Hi, I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings: CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) This works very well, other than instances of the delimter are, themselves, considered to be results. For example: SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result returns: pn s 1 foo 2 bar which is exactly the result I would want. However, SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar' returns pn s 1 2 foo 3 bar 4 And SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar' returns pn s 1 foo 2 3 4 bar I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course. In other words, all three examples above should produce the same result: pn s 1 foo 2 bar How can I do this? Any thoughts much appreciated...
View Replies !
Break Fullname Into First And Last Name
Hi, Guys, I need to break a column Fullname into two columns called Firstname and Lastname. The Fullname column contains users fullname with a space between first and last name, for example (jonh smith). I want the first name to be in Firstname and last name in Lastname. Can any one help me on this? Thanks in advance.
View Replies !
Break Fullname Into First And Last Name
Hi, Guys, I need to break a column Fullname into two columns called Firstname and Lastname. The Fullname column contains users fullname with a space between first and last name, for example (jonh smith). I want the first name to be in Firstname and last name in Lastname. Can any one help me on this? Thanks in advance.
View Replies !
Reasonable To Assemble FullName W/ A UDF
I break somone's name in a table in fields as First, Last, Middle,Suffix.Is it reasonable to use a UDF to create the full name as a string if Ineed it, by passing it the First, Last, Middle, & Suffix fields?Is this really slow to do? It's just sometimes I want to do a GroupByQuery and I want to include the person's name in the Select of thequery and I don't know if the group by would work if I returned eachcolumn seperately.Any opinions on this?Thanks.
View Replies !
Splitting Values
I have a sql2005 db. I'm trying to perform a query that takes a list of values in a parm, breaks them up and uses them in an IN clause as follows: <code> ALTER PROCEDURE [dbo].[SelectPartialOrderListByDate] @StoreID varchar(255)ASBEGIN SET NOCOUNT ON; SELECT DISTINCT O.OrderID, M.InitialSalesCode, M.DateOccurred, M.ErrorCode FROM dbo.OrderInfo O WITH (NOLOCK) INNER JOIN dbo.Message M WITH (NOLOCK) ON O.OrderID = M.OrderID WHERE O.StoreID IN (@StoreID)END </code> The values coming into the procedure are obviously in a varchar string format. Notice the boldfacing above. The values within this IN clause should be something like this after the conversion: IN (87,108). How can I accomplish this? I've seen examples of creating a UDF and returning a table, but am not sure how to apply that to my situation via a code sample. Could someone help me out?
View Replies !
Splitting Of Database
hiI am new to SQL.I have database of 30GB.I have just heard aboutspliting Databases which helps in Performance.So please can any oneguide me in what are the steps involved in it.Anxious to know How does it work if i split my database in twodifferent location.We are using SQl 2000.Operating System - Windows 2000 serverregardsTV
View Replies !
Striping And Splitting
SQL Server 2005 SP2I read the excellent advice"Optimize tempdb in SQL Server by striping and splitting to multiplefiles" athttp://searchsqlserver.techtarget.c...1276989,00.htmlMy question: All of my database files, primary and log, are on a SANthat is using RAID 10 -- so these files are striped and mirrored acrossmultiple spindles anyway.Will I get any advantage from creating multiple files per processor inmy filegroups, as the article suggests?Will SQL Server be smart enough to use multiple workers (threads?) witha single file, or do I have to signal that it should do that by creatingmultiple files in my filegroups?TIAaj
View Replies !
Splitting A Field Into Two
I have a field that contains codes likefhj#asdskjjljlj#12And so on.What I want to do is create two new fields (field1 and filed2) thatsplit the original filed at '#'If a field does not contain '#' I would like its entire contents to besaved in field1.Also how do I ensure that I save these changes?Thanks fo any help in advance.Regards,Ciarán
View Replies !
Column Splitting
Hi Everyone,I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.sample data:create table tbl_list(pk_int_itmid int(5) Primary Key,vchar_desk vchar(300));create table tbl_test1(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itm varchar(60));insert into tbl_list values(1, 'this item');insert into tbl_list values(2, 'that item');insert into tbl_list values(3, 'those items');insert into tbl_test1 values(1, 'A, B - C, D, E - F, G, H - I');insert into tbl_test1 values(2, 'J, K - L, M, N - O');insert into tbl_test1 values(3, 'P, Q - R');into this table:create table tbl_output(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itmA varchar(60),vchar_itmB varchar(60),vchar_itmC varchar(60));Output in comma delimited form:'1', 'A', 'B', 'C''1', 'D', 'E', 'F''1', 'G', 'H', 'I''2', 'J', 'K', 'L''2', 'M', 'N', 'O''3', 'P', 'Q', 'R'my current solution:create view vw_itm_a as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom tbl_test1where charindex(',',vchar_itm) >1Gocreate view vw_itm_b as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom vw_itm_awhere charindex(',',vchar_itmC) >1;Gocreate view vw_itm_c as select fk_int_itmid, substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmCfrom vw_itm_bwhere charindex(',',vchar_itmC) >1;Go;create view vw_itm_d asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm_e asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm asselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_awhere fk_int_itmid not in (select fk_int_itmid from vw_itm_b)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_dunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_bwhere fk_int_itmid not in (select fk_int_itmid from vw_itm_c)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_eunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_cGo;select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC into tbl_outputfrom vw_itmIs there a much more efficient manner of handling this column splitting?ThanksDC
View Replies !
Splitting Decimal Value
I need to send an extract to a vendor, and for some reason they want the following: 999.34 - StrikePrice sent as 999 - StrikeDollar 34 - StrikeFract Tried using various conversions, but at the moment can't solve. Thanks.
View Replies !
Splitting Datafiles
I founded couple thread about splitting but not find same situation what i have. I have server with Raid10 with big storage massive, and Test server with two 30Gb HardDrives (not into a Raid or Somethink). I have situation when in real server database grown over 30Gb and now i can not restore database copy into a test server because i have one big Data File. Can i split somehow 35Gb Data file when i restore to test server to 25Gb and 10Gb ??? Or can u recomend some solutions At the moment cant to do hardware upgrate.
View Replies !
Splitting Headache
Hi, I have a system that imports data from a textfile weekly, it then returns all faulty rows to the source. The system contains pricing data and agreement dates. One of many faults can be faulty "split". A split occurs when a part is bought for example 40% from one manufacturer and 60% from another. There can also be different prices for different months from the same manufacturer. manufacturer. I use "AGR_DATE_FROM+AGR_DATE_TO+PN+FACTORY+[QTYFROM/DATEFROM]+[QTYTO/DATETO]" as unique indentifier for each row. AGR_DATE : The date for the agreement. PART_NUMBER: Unique number for this part FACTORY: Where it's manufactured QTY/DATE : A part can have a different price for different dates or for different quantities. ---- I have constructed a sp that spits out all faulty rows where the total split is not 100%. But the logic fails when there is more than two suppliers during a period. It very seldom is but it occurs from time to time. Example that fails: AGR_ID.AGR_DATE_FROM.AGR_DATE_TO.PN.........FACTOR Y.SPLIT.DATE_FROM..DATE_TO......PRICE AA11....2004-01-01........2004-12-31....456545/1.FACT1....40.....2004-01-01...2004-02-29...2,8 AA11....2004-01-01........2004-12-31....456545/1.FACT1....40.....2004-03-01...2004-03-31...3,25 AA11....2004-01-01........2004-12-31....456545/1.FACT1....40.....2004-04-01...2004-06-30...3,6 AA11....2004-01-01........2004-12-31....456545/1.FACT1....40.....2004-07-01...2004-09-30...3,6 AA12....2004-01-01........2004-12-31....456545/1.FACT1....40.....2004-10-01...2004-12-31...3,8 AA13....2004-01-01........2004-12-31....456545/1.FACT1....60.....2004-01-01...2004-03-31...2,6 AA13....2004-01-01........2004-12-31....456545/1.FACT1....60.....2004-04-01...2004-06-30...3,1 AA13....2004-01-01........2004-12-31....456545/1.FACT1....60.....2004-07-01...2004-09-30...3,1 AA13....2004-01-01........2004-12-31....456545/1.FACT1....60.....2004-10-01...2004-12-31...3,1 ---- In this case the rows are NOT faulty but gets spit out... I have omitted my sp because I want some fresh input The function I'm after is to delete all rows on one partnumber where the total split during the agreement dates is not 100% Can anyone help me? Because I've got a splittingheadache...
View Replies !
Splitting Dates
I have a table that contains agrrements and contracts with dates. Now I need to calculate some things and I'd like the rows to only have one month per row. I have rows like: Agreement, Start, End ID001, 2004-01-01, 2004-04-30 If I could get these single rows that contains 4 months into a temptable like this: Agreement, Start, End ID001, 2004-01-01, 2004-01-31 ID001, 2004-02-01, 2004-02-29 ID001, 2004-03-01, 2004-03-31 ID001, 2004-04-01, 2004-04-30 It would simplify my calculations very much. How to do this with a query or sp?
View Replies !
Splitting A Table.
I need to split a field that contains a 4 digit number, into two tables each containing a 2 digit number. This is for an SSIS package to import data from a text file. Thanks folks.
View Replies !
Splitting PDF Documents
This may be a stupid question but I'll throw it out here, is it possible to use sql 2005 to split up pdf files into individual files by a field on the form or an index? Thanks.
View Replies !
Splitting Strings
I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL. I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet. What I have done is this. I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server. On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[stp_SecurityAuditReport] AS TRUNCATE TABLE SecurityEvents_Tmp -- Parse Bank Number & UserName INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data) SELECT DepartmentNumber = '001', UserName = CASE WHEN Strings LIKE '[0-9][0-9][0-9]%' THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1) WHEN Strings LIKE '-|[0-9][0-9][0-9]%' THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3) WHEN Strings LIKE '-|[a-z]%' THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3) WHEN Strings LIKE 'Account Unlocked. |%' THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21) ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1) END, Events.* FROM Events JOIN EventsToLog on Events.EventID = EventsToLog.EventID WHERE SID NOT LIKE 'S-%' -- Update blank usernames UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME' WHERE UserName = '' OR UserName = '-' -- Update DepartmentNumbers with zeros UPDATE SecurityEvents_Tmp SET DepartmentNumber = CASE WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR UserName LIKE '[0-9][0-9][0-9]#%' OR UserName LIKE '[0-9][0-9][0-9]$%' THEN SUBSTRING(UserName,1,3) ELSE '001' END As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports. I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!) DECLARE @TimeGenerated datetime SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated DECLARE @TimeGeneratedEnd datetime SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL THEN 'All Department' ELSE b.DepartmentName END, a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4 FROM SecurityEvents_Tmp a LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber JOIN EventsToLog d on a.EventID = d.EventID WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist) AND a.TimeGenerated BETWEEN @TimeGenerated AND @TimeGeneratedEnd ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated This combination of utils and scripts does very good for producing generic security reports for branch officers. But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from. This is the Strings field from a failed logon (529) 200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397 And this is from Event ID 642 which was an account being created. -|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|- Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account. So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string. I could then use a case to get the information needed. HELP PLEASE!! Thank You, John Fuhrman http://www.titangs.com
View Replies !
Splitting A Database
I have a large db (sql 2005 express) that is, well, basically a mess. It is not normalized properly and contains masses amount of data (due to so much repeating data). To make a long story short - this db needs to be redesigned, but management said no, so that is not an option - so please, no one suggest that that's what I do. My application creates reports based on this db - the problem is, the sp's are slow - and when a report needs to run several reports, it takes a long time to run. The sp's and db have been optimized as best I can (adding indexes etc.,) I was wondering if there is a way to split the db - what I want to do is just retain, say 2 years of data in 1 db, and store the rest of the data in the other db, as 2 years worth of data is 95% of what will be queried. I did copy over 2 years worth for testing, and reports that took 30 minutes in the existing db, take less than 1 minute (sometimes even faster) in the new db - a huge improvement. My problem is how to deal with the times that I need more than 2 years worth of data - how do I query both db's to get my application to read the data from both db's so that it seems that I am only running one db? The new db would be updated daily with new data, but not the old db - so if I had to query 10 years worth of data, I need 2 years from the first db, and then the 8 yeas from the second db. If anyone can provide some feedback or point me in the right direction of what I should research in order to accomplish the above - I would appreciate it. If anyone knows of a better solution - please don't be shy - speak up! :) Thanks - ann
View Replies !
Splitting Out A Fields Value
Hi I have a field that has the following value 001, 002, 003, 004 I want to select the different value as col col2 col3 col4 001 002 003 004 Sometimes there might only be one value, sometimes none sometimes two, three or four Thanks
View Replies !
Need Help Splitting Contents Of A Field In T-Sql
I have a table with about 2 million records. One of the fields has data seperated by a comma. I need to be able to grap the data for each record and split those items into their own table in seperate rows. This is easy in Asp but the page will timeout before it can process all the records. Any Ideas? Thanks,Rick
View Replies !
Splitting A Filename Out Of A Filepath
I have to create a view where the filename is seperated from the pathto the file. Examples of the data include:m:imagesigmyimg.jpgm:imagesmediummyimg.jpgz:mediaimageshighqualmyimg.jpgIs there a function that will return the position in a string of thelast ? With this I can easily use substring or similar to pull andpush this stuff into the fields I want. If this function does notexist how do I move forward?
View Replies !
Splitting Directory And File Name
First of all apologies for posting this again but I posted it in thewrong place the first time.Can anybody help me with the post below?http://groups.google.com/group/SQL-...5ec9f04d978d0deThanks,Paul
View Replies !
Splitting 9 Digit Zip Codes
hey all i am stuck with this little problem I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565' is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash? thanks
View Replies !
Splitting TempDB Across Two Volumes
I will soon have two RAID 5 volumes dedicated to SQL Server with one production database on a server. I will be splitting up the transaction log and indexes onto seperate volumes and possibly assigning certain tables to the new volume. What I am curious about is the distribution of tempdb. Tempdb is used quite a bit in a nightly load process that takes about 3 hours. So, optimizing tempdb would be a good thing. If I just put a new datadevice on the new volume and extend tempdb to use it, I don't think that it will distribute properly. And, because the tables are created on the fly, I can't assign certain tables to different volumes. So... here are my questions: - Will SQL Server 6.5 balance the distribution of tempdb if I just put it on the two different devices on the two volumes? - Is there a better way to do it? - If so, how? Thanks
View Replies !
Splitting A Text Field
hi, I am trying to split a text field . what I want to do is - split a text field and insert chars 1 to 8 in one field and then from 9 to 20 in another and so on. Can someone help me in solving this. TIA. PD
View Replies !
Problems Splitting Name Field
I am reading in data from a legacy system where name data is in one field. The single field includes last name, first name, middle initial(sometimes), and suffix(sometimes). Due to the optional components it is a bear. I am using a fuzzy lookup on the suffix and derived columns for the first, last, and middle initial. The first name and suffix work, but I'm having trouble with the last and middle initial. The following is the formula I'm using for both. The first name formula is causing an error, and the middle initial is only populating if the suffix exists. If anyone has insight, it is appreciated. FirstName: TRIM(SUBSTRING([ARPNAME-T],FINDSTRING([ARPNAME-T]," ",1) + 1,FINDSTRING([ARPNAME-T]," ",2) - FINDSTRING([ARPNAME-T]," ",1) + 1)) MiddleInitial: SUBSTRING(TRIM([ARPNAME-T]),FINDSTRING([ARPNAME-T]," ",2) + 1,(LEN(TRIM([ARPNAME-T])) - FINDSTRING([ARPNAME-T]," ",1) - LEN(PSUFFIX)))
View Replies !
Splitting Up Columns Mid-process
When I accept my input data, it's convenient to read it in from a flat file as a fixed width file with a width of 100. Then I run a splitter to split the input into two different paths (path_1 and path_2, say), based on an indicator field. The output of path_1 has 1 column with width of 100, but now I want to split it up into lots of little columns. The output of path_2 also has 1 column with a width of 100, but I want to split it up, too, only using a different fixed width map than i used for path_1. Any ideas? Thanks! Jim Work
View Replies !
Splitting A Table In Half
Hi all, I am trying to create a table of counts but it is running too long and would be a waste of paper so in essence I want the table to wrap at the 50% mark. As far as I understand theres no way to do that with just one table in RS so Im planning on using two tables and filtering them somehow. I have been looking at the top % and bottom % filters but they dont seem to work for my purposes. Heres what I got: Program Count Program 1 40 Program 2 25 Program 3 23 Program 4 18 Program 5 10 Program 6 5 Program 7 1 Heres what I want to do: Program Count Program 1 40 Program 2 25 Program 3 23 Program 4 18 Program Count Program 5 10 Program 6 5 Program 7 1 gte 50% lt 50% Does anyone know an easy way to split a data set by group at the 50% mark? Thanks!
View Replies !
Data Splitting Between Pages
In my report I have a table that contains seven groups. The last two groups are used to display a majority of the data. The issue is that the last grouping is actually mimicking the detail row of the report where the cells are summing data to display values of the group. In certain instances the page will break with the first group€™s rows on one page but not the final group€™s rows. However, on the next page I am repeating the first group€™s rows so there it outputs correctly. Is there any way to set it up so that a situation like this does not occur?
View Replies !
|
TRACKER
