Can I Split A Field Based On A Character?
Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.
View Complete Forum Thread with Replies
Related Forum Messages:
Separating One Field Into Two Fields Based On A Character In The Field
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View Replies !
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
Conditional Split Based On Conditions
Hi, I have the following table in MsAccess EmployeesA empId integer, empName varchar(60), empAge integer, empStatus char(1) - can be N,D or S - New, Deleted or Shifted and the following in Sql2005 EmployeesB Id smallint, Name varchar(60), Age int, Status char(1) - Bydefault 'N' I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now. If empStatus = N in EmployeesA, then insert a new record in EmployeesB If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D' If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row. How do I do it for each table each row in EmployeesA using a foreach file loop? Thanks, lolsron
View Replies !
How To Split One Report Into Many File Based On Volume.
Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,). Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees. Can anyone suggest a way to do this€¦ Regards, Krishna
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on this
View Replies !
Retrieving Data From A DB Based On Output Of A Conditional Split
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need. Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing. Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command. How can I do this? Thank you! Nolan
View Replies !
How To Return A Substring Based Upon A Character In The String ?
Is their a way to return only a portion of a string returned from a query? Please look at the following query and what it Yields: select UserName from MyDirectory NTSRJoe MeekletonNTSRAnfreesa ClinklHeadBRYAmy KooplosaAKVermian Strikoos How can I fix this query so that it returns everything to the right of the '' character? So I want the query to instead return the following: Joe MeekletonAnfreesa ClinklHeadAmy KooplosaVermian Strikoos
View Replies !
Split A Field
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View Replies !
Multiple Foreign Keys On Same Field, Based On Other Field
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI =============================================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance ===================================================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
Need Help: Count A Field Based On Field Criteria
Hi, I can't seem to get my head around this, hopefully someone can help. In my query I have a few fields, which I would like to get the number of rows based on certain criteria. For example I have a gender field, and I would like to get the number of females or males from the query. I have been trying the following method, which uses the Sum and Iif functions: Code Block Sum(IIf(Fields!gender.Value = "Female", 1, 0)) This does bring back a figure, but it's not correct. My query brings back 46 rows, yet the expression only brings back a figure of 24. I have even tried doing a query using CASE statements, bringing back just one row with all the correct figures in. But if I then add a textbox, with the expression '=Fields!gender.Value' it also brings back 24, yet the fields actual value in the query is 46. What is causing this problem? Is there a way around this? Thanks
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Split One Field In Multiple New Rows
HiHo, just a beginners question: I have the following row with 2 fields: Field 1: Task A Field 2:´ 1;2;3;4 The number of semicolon divided elements in Field 2 is variabel. I would like to create new rows like: Row 1 Field 1: A Field 2: 1 Row 2 Field 1: A Field 2: 2 Row 3 Field 1: A Field 2: 3 Row 4 Field 1: A Field 2: 4 I think I should use a Foreach Loop. But I don't exactly how to do it? best regards Chris
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Split Address Into Multiple Field
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start? lets say i have "12 north plaza boulevard apt.16" and i want it to become: address_number 12 suffixA North street_name plaza blvd suffixb apt16
View Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
Hi. I'm trying to write an SQL Query that will take a delimited field and return each item as a row. Example Take the AuthorizedRoles and TabID fields from the Tabs table AuthorizedRoles TabID 0;11;__________1 0; 15 ;17;______6 -2;____________7 I would like to return a unique record for each Authorized Role AuthorizedRole TabID 0____________1 11___________1 0____________6 15___________6 17___________6 -2___________7 Any ideas? Cheers Dave
View Replies !
Max Character In Field
In a SQL Server Db, what is the maximum amount of data a field can hold? I am using right now nvarchar with a length of 4000. Is there another setting to allow even more in a field? Thanks all, Zath
View Replies !
Numeric Value Only For A Character Field
HiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
View Replies !
Character Field Formats
I was wondering what everyone felt about the fomats in characterfields where the front end application accepts anything.I wouldn't want a customer table where the customer name was lowercase on one, upper on another and who knows on the third.If character fields are not consistent, then formatting will have tohappen every time someone access the data for reporting - as anexample ...Thanks,Craig
View Replies !
Order By On Character Field
Does ORDER BY work on character data type in SQL Server through ODBC? I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results. Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name company_id = integer company_name = 30 characters
View Replies !
I Only Get The First Character Of The Field Returned .. What Am I Doing Wrong ?
here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C public string GetUserName(long UserId) { try { string userName; DiscussionDB data = new DiscussionDB(); List paramlist = new List(); paramlist.Add(data.CreateParameter("@UserId", UserId)); paramlist.Add(data.CreateParameter("@UserName", "", ParameterDirection.Output, DbType.String, 20)); data.ExecuteNonQuery("dbo.Discussion_User_Name", ref paramlist); userName = paramlist[1].Value.ToString(); return userName; } catch { throw; } } ALTER PROCEDURE [dbo].[Discussion_User_Name] @UserId bigint, @UserName varchar(20) output AS SET NOCOUNT ON SET @UserName = (Select [Name] from Discussion_Member WHERE UserID = @UserID) if (@UserName is null or @UserName = '') BEGIN SET @UserName = (Select UserName from Membership_User WHERE UserID = @UserID) END
View Replies !
Field Delimited By A Special Character
Hi All, When using bcp, you delimit a field by the switch -t .. -t -- tab delimited,etc.. Is it possible to define a special character? In this case this special character is formed when you do 'select CHAR(1)' ... it generates the . Can we do something like -t1 ??? or -t01 .... Thanks in advance. David
View Replies !
Converting 6 Character To Date Field
I have a 6 char field which has to be converted to a datetime. I thought I had it solved when I did this convert(datetime,(left(dob,2)+'-'+substring(dob,3,2)+'-'+right(dob,2))) Problem is with a date value of 081649 I get 08/16/2049 instead of 1949, where did I goof
View Replies !
Convert With Character Field And Datetime
Hi, I am getting a date string that is contained in a character field (char(20)). An example of the data is as follows: 2005-09-20121315001 it is in the format YYYY-MM-DDHHMMSSMMM. I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon i.e. to get it to insert into a datetime field the data has to be in the following format 2005-09-20 13:15:18.001 YYYY-MM-DD HH:MM:SS.MMM Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date, Thanks in advance
View Replies !
Replace A Character Inside A Field
Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19 So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes How can I do this? Any help? Thanks
View Replies !
Quoted Field And Escape Character Issue
I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field. So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?
View Replies !
Oledbcommand Converting Date Field To Character Datatype
im usoin sqlbuklcopy class to migrate dbf files into sql server tables, but i got a proble and its because the format of a field named birthdate its mm/dd/yyyy and i got this Cadena = "select SITE_ID ,PATIENT_ID , LOCAL_ID, " & _ " BIRTHDATE , GENDER , LAST_NAME , FIRST_NAME from patient " then i open mi oledbcommand Dim cnSource As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Origen & ";Extended Properties=DBASE IV") Dim cmdSource As New OleDbCommand(Campos, cnSource) and throw me an exception referring the format of birthdate field...how can i convert in my select statement the field birthdate to a character datatype?
View Replies !
Getting One Field Based On Two Other Parameters
I have a report. I want to display title according to the following combination of two parameter values. Line of Business Product Type Report Title Total Line, Loan 13 Month Home Equity Portfolio - Total Cbank Line, Loan 13 Month Regional Bank Portfolio - Total DTC Line, Loan 13 Month Direct to Consumer Portfolio - Total Edirect Line, Loan 13 Month Equity Direct Portfolio - Total InstLend Line, Loan 13 Month Institutional Lending Portfolio - Total Total Line 13 Month Home Equity Portfolio - Lines Cbank Line 13 Month Regional Bank Portfolio - Lines etc Line of Business and product type are two parameters. Product type is a muliple parameter. i.e you can select Line, Loand or Line and Loan. can any one tell me how to write a proceedure to get this result(i.e report title) Thanks
View Replies !
Retrieving Data Based On Value In A Field
Hi I hop i make sense with this, i have a page where you can update a products price based on the year, so for instance some products will still have prices set in 2007, while some may have prices which will be set in 2008, what i am trying to do is if the price has been set in 2008 it shows this value. The fields in the table which control the values are Rate(controls 2007), and Rate2007Period2. What i am trying to do is if Rate2007Period2 is null or 0, then display the 2007 rate, and if Rate2007Period2 is greater than 0 then display that value. Here is the view which displays the rates; SELECT dbo.tblRateSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblRateSchedule.DESCRIPTION, dbo.tblRateSchedule.SWT, dbo.tblRateSchedule.Rate, dbo.tblRateSchedule.Tstamp, dbo.tblRateSchedule.RateSchedule_ID, dbo.tblRateSchedule.RateScheduleUnit_ID, dbo.tblRateSchedule.RateScheduleType_ID, dbo.tblRateSchedule.WorkType_ID, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description, dbo.tblRateScheduleType.Type, dbo.tblRateSchedule.Work_Type_Description AS Expr1, dbo.tblRateSchedule.Rate_Active, dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2007Period1, dbo.tblHistoricalRateSchedule.Rate2007Period2FROM dbo.tblRateSchedule INNER JOIN dbo.tblHistoricalRateSchedule ON dbo.tblRateSchedule.MA_Code = dbo.tblHistoricalRateSchedule.MA_Code LEFT OUTER JOIN dbo.tblRateScheduleType ON dbo.tblRateSchedule.RateScheduleType_ID = dbo.tblRateScheduleType.RateScheduleType_ID LEFT OUTER JOIN dbo.tblWorkType ON dbo.tblRateSchedule.WorkType_ID = dbo.tblWorkType.WorkType_ID LEFT OUTER JOIN dbo.tblRateScheduleUnit ON dbo.tblRateSchedule.RateScheduleUnit_ID = dbo.tblRateScheduleUnit.RateScheduleUnit_IDWHERE (dbo.tblRateSchedule.Rate_Active = '1')
View Replies !
Calculating A Field Value Based On Other Records
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship. Table1- the primary key table has the following columns ContentID - int identity field DateAdded- datetime Table2 - The foreign key table, has the following relevant fields FK - int foreign key version - int When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK. Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version. I would appreciate any ideas. Thanks, laura
View Replies !
I Need A Sp That Will Update A Field Based On Conditions
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example; @ClientID Int, @PmtAmt Money IF @PmtAmt >= tblSettings.TopAmt THEN Update tblClients SET ClientStatus='High' WHERE ClientID=@ClientID ELSE Update tblClients SET ClientStatus='Medium' WHERE ClientID=@ClientID ENDIF How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
View Replies !
|
TRACKER
