Transact SQL :: DATALength - Split A Field Based Off Its Length
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,'')
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8Â Â Â Â Â Â *Correct 1234567-9 -> 1234567-Â Â Â Â Â *Wrong 123456789 -> 123456789Â Â Â *Correct 1234567#9 -> 1234567#Â Â Â Â *Wrong 1234567a9 -> 1234567a9Â Â Â *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
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.
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.
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
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.
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5. Desired data format: Name1 Name2 Name3 Name4 Name5. Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally Bush/Jim/Sr/Etal/Trustee
I simply need to list the most recent value for each employee here... the value at each person's maximum date
Sample Detail Data:
EmpID                 Date                    Value 1                             1/1/14                 27 1                             2/12/15               333 2                             5/5/15                  255 3                             5/4/15                  110  3                             1/1/13                  67                          3                             3/2/14                  80
[Code] ....
What is the most efficient way to display the most recent value for each employee ID via MS SQL.?
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
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?
Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)
Basically, I have the following table structure Employee(employee_number, continuous_start_date, ...)
The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+). I can work out the length of service in months with the following code
SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months' FROM employee
So the first stage is to summarise the length of service into groups as mentioned above.
Then the final stage is working out how many people are in each group!
Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)
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.
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.
I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
I have a database on sql2000. I want to change the length of a field in Table A, but it is related to another field in Table B. When I tried to change the length of the field in Table A via
ALTER TABLE table ALTER COLUMN field varchar(5).The query analyzer generates the following error.
Server: Msg 5074, Level 16, State 8, Line 1 The object 'UQ__table__77BFCB91' is dependent on column 'field'. Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN field failed because one or more objects access this column.While when I tried the same query on the field in TABLE B, the query completed without any errors.
For example sake I removed the actual text, but this gets the point across as to what I want to do, but it throws this error
Msg 103, Level 15, State 4, Line 2 The identifier that starts with 'abc cde fghijkllmalama aljkjlkj alkjkljlkjkljk aljkljlkljk aljkascnalk alkjasdlkjlkajsd asdljklkasdjflkjasdf alkjasqquiouoie oiu' is too long. Maximum length is 128.
I am looking for a query that can search an entire database and return max length of all nvarchars in a database. I can write a cursor but its a pretty big database with  lots and lots of columns using nvarchars with most of them having a length of around 300 and doing a select max(len(nvarchar_col) from table from few tables return only 12 or 13. So I want to restrict the length of this data type accordingly.
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.
There are two fieldsA1 nvarchar(30)A2 nvarchar(800)I know nvarchar field is alterable length, if I store a string mystring='abc' to A1 field or to A2 field, I think they use the same disk space, so I think it's always a good way to define a big length nvarchar field such as A3 nvarchar(4000) for any length string, becuase they always use the same disk space, is it right?
I need to expand the size of one column which has been defined as varchar(32) to varchar(50).Is this possible?Already there are many old records in the table,in what way it will effect the old records?Any help is appreciated. Thanks!!
Hi, all I am seting up a table with email message, I am wondering what is the max length for varchar field. I am so reluctant to use text field, since when I run query for the descriptiona in sql analyzer, text field cannot be fully display in column. Any tricks to share? Thanks Betty
If I want to make a field of characters to be unlimited length(or maybe 2k for example), what datatype should I use? Char, varchar and text have a max. limit of 255...
I have to modify field length in the table, which is part of publication. I tried to use an Enterprise Mgr and received an error message because that table is scheduled for replication. Is any way around this beside temporary stopping and restarting replication? And actually how to do this to be sure that after restart the data will be again in sync. We have SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server.
