Can I Split A Field Based On A Character?
Nov 9, 2005
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 5 Replies
ADVERTISEMENT
Dec 4, 2013
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
View 1 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
May 15, 2015
I need to split a field based off it's length. Â Initial thought was to take Left(field, characters), and Right(field, characters), but now I see I am duplicating the data as I am capturing too much of the data in. Â However, what I actually need to achieve is in field1 capture the left 1000 characters, and in field 2 take from character 1001 to the end. Â How can I update my syntax so that field2 will ONLY capture from character 1001 to end?
field1 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN LEFT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) ELSE exitinterviewnotes END,''),
field2 = COALESCE(CASE WHEN DATALENGTH(exitinterviewnotes) > 998 THEN RIGHT(CAST(exitinterviewnotes AS VARCHAR(MAX)), 1000) END,'')
View 5 Replies
View Related
Dec 8, 2014
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = (select a_notes
from dbo.notestuff as notes
where a_id = '1')
[Code] .....
View 5 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Apr 15, 2004
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
View 3 Replies
View Related
Jan 24, 2006
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 4 Replies
View Related
Mar 25, 2015
I have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE
Looking for split function that accepts a multiple character value as a delimiter?
View 7 Replies
View Related
Feb 20, 2007
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies
View Related
Oct 9, 2001
I have a table with column person_name and I want to update that table and
insert the values of different columns FIRST_NAME and LAST_NAME.
Anybody can tell me the T-SQL code to do above problem?
Thank you very much!
View 3 Replies
View Related
Jun 24, 2014
My code looks like this for now:
Code:
SELECT CDELIG.CDELIG_QTE AS [Quantity],
(ARTICLE.ART_PRIXPUBLIC * CDELIG.CDELIG_QTE) * 100 AS [Total line price],
(CDELIG.CDELIG_PU_SUBACC_MNT * CDELIG.CDELIG_QTE) * 100 AS [Grant]
FROM CDELIG
INNER JOIN ARTICLE ON ARTICLE.ART_ID = CDELIG.ART_ID
And the result like this:
Quantity Total line price Grant
120 84000 0.0000
255 178500 0.0000
1 700 0.0000
The problem is that if a quantity is higher than 99 i got to split the row. And the others has to change also because the quantity would have change.The expected result will be like this:
Quantity Total line price Grant
99 69300 0
21 14700 0
99 69300 0
99 69300 0
57 39900 0
1 700 0
View 6 Replies
View Related
Sep 23, 2007
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 6 Replies
View Related
Sep 5, 2014
I need to split a row into multiple rows based on multiple column values.
time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00
I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
row 1
time_in 10:00
time_out 12:00
row 2
time_in 13:00
time_out 14:00
row 3
time_in 15:00
time_out 16:00
View 2 Replies
View Related
May 4, 2007
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 1 Replies
View Related
Feb 5, 2007
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 2 Replies
View Related
Sep 5, 2007
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 1 Replies
View Related
Apr 29, 2008
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 2 Replies
View Related
Jul 8, 2015
I have a field in a table that contains addresses e.g
15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road
I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.
So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.
View 6 Replies
View Related
Aug 5, 2015
equipmentid downtimestartdate downtimeenddate  dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 Â Â Â Â Â Â 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 Â Â Â Â Â Â 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er      2015-03-15 02:00 2015-03-15 24:00       22Â
a3er      2015-03-16 00:00 2015-03-15 24:00       24
a3er      2015-03-17 00:00 2015-03-15 23:00       23
[code]...
View 2 Replies
View Related
Jul 23, 2005
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 4 Replies
View Related
Jul 23, 2005
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 26 Replies
View Related
Jul 16, 2015
I would like to know how to split the phone number into two columns based on - symbol Dynamically.
for example
Phone Number
123-12323
1234-1222
so output should be
code number
123 12323
1234 1222
View 1 Replies
View Related
Mar 23, 2007
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 4 Replies
View Related
Apr 14, 2006
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 4 Replies
View Related
Apr 11, 2006
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 2 Replies
View Related
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 Replies
View Related
Aug 1, 2004
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 1 Replies
View Related
Aug 27, 2014
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
View 1 Replies
View Related
Jan 30, 2015
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
View 1 Replies
View Related
Sep 4, 2015
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. Â I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT Â isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Jun 7, 2002
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 9 Replies
View Related
Aug 3, 2006
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 1 Replies
View Related
