I have a field that contains codes like
fhj#asds
kjjljlj#12
And so on.
What I want to do is create two new fields (field1 and filed2) that
split the original filed at '#'
If a field does not contain '#' I would like its entire contents to be
saved in field1.
Also how do I ensure that I save these changes?
Thanks fo any help in advance.
Regards,
Ciarán
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
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
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)))
I am able to use the following SQL Statement in Access, but when I try to use it in a View or Stored Procedure for SQL Server 2000, it says the function InStr is not a valid function. Would someone be able to assist me with modifying this query to work in SQL Server 2000. This is Spliting a field called EName into two fields called FName and LName.
SELECT EmployeeID, CountryCode, Branch, SSN, Title, HireDate, Status,
RIGHT(EName, LEN(EName) - InStr(EName, [, ]) - 1) AS FName, LEFT(EName, InStr(EName, [, ]) - 1)
AS LName
FROM dbo.Staging_Employees
Thank You,
Wayne
Hi
I have a customer data in a flat file (*.csv). It has a field "Name" which has data as James Smith. I want to load the dat in the dimenson table as fields FName="James" and LName="Smith". What type of data transformation should i use and how should i make it happen?
thanks,
chamajid
I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"
How do I get the "01/01/1900" between ":" and ")"
I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
have a field in a table that has combined lastname,firstname and middle name like
combs,albert mike
woods-athere,jane alice
The last and first name are separated by a comma, and the middle name by a space
I need in tsql to split them to last name,first name and middle name
I used below
select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname',
substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname',
substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename'
FROM Demographic_staging
I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space
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?
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
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 1 Replies View RelatedI 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.
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.
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?
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
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
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
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 2 Replies View RelatedHello,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 1 Replies View RelatedI've read that we need to split data into a training and testing data set. How do I go about that?
View 7 Replies View RelatedThis 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.
Hello
I have a column which contains a phone number followed by a name.
How can I split off the name.
Example 0898 500 400 Joe Bloggs
Thank you.
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
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.
is there any builtin functions or any other ways to split digit and numbers in sql server2005.
for example in Patel1234 , i am want to split patel and 1234 seperately.
thanks,
I use to use the Instr() method, but that is not allowed in SQL..
Any suggestions?
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.
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
I have 5 records in a table called "testing" as such:
LetterCdcontact_id
x1
x2
x3
x4
x5
How can I split this dataset so that three records have LetterCd = 'y' and the other 2 records have LetterCd = 'x'? The number of records can vary, but the idea is the same: split the values in the LetterCd column evenly (50% = 'x', 50% = 'y'). LetterCd is varchar(25) and contact_id is int. I can't update the data, it would have to be a SELECT statement with some sort of CASE statement to get the remainder of the record count?
Running SQL 2005
Current Query is as follows:
SELECT
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,
[code]....
This query was working well because I used to only be interested in one counter that was returned in the column, which was 'Free Megabytes'...I now have additional data that shows up as 'Total Disk Space'...Ideally, the query would return the total disk space next to the free megabytes on the same row for the same disk drive. Here is a couple rows of sample output:
AverageValueInstanceNameObjectNameCounterName
44549 C: LogicalDiskFree Megabytes
44548 C: LogicalDiskFree Megabytes
69452 C: LogicalDiskTotal Disk Space
69452 C: LogicalDiskTotal Disk Space
This is the ideal format, the average value column goes away:
InstanceNameObjectNameFree MegabytesTotal Disk SpaceC: LogicalDisk44549 69452
I had a query regarding splitting MDF files. As one of our databases has an mdf sized 75GB, I was planning to split it into 2 or 3 files and put them across different RAID disks. But I could not find a thorough source for information as to how to go about splitting an existing MDF file into smaller files. Can anybody help?
View 5 Replies View RelatedI have a table like this:
name | purchase amount | purchase date |
Joe--------20.00--------------12/1/06
Joe--------10.00--------------12/1/06
John-------10.00--------------1/15/06
Sam--------15.00--------------5/10/06
John-------20.00--------------11/1/06
Joe--------5.00---------------8/19/07
Sam--------5.00---------------10/8/07
Sam--------10.00--------------4/22/08
Sam--------15.00--------------4/22/08
and what I need is a query that will show name and the sum of purchase amount for specific years. Here is what it would look like:
name | purchase 2006 | purchase 2007 | purchase 2008
Joe 30.00 5.00 0
John 30.00 0 0
Sam 15.00 5.00 25.00