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,'')
View 5 Replies
ADVERTISEMENT
Aug 28, 2015
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.
View 3 Replies
View Related
Oct 10, 2005
Hey all.
I'm looking around for a solution on how to pull records back based on legnth.
So, if field A is more then 2 characters, return that row.
Thanks
Caden
View 2 Replies
View Related
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
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
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
Sep 18, 2015
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
View 43 Replies
View Related
Jun 18, 2015
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.?
View 4 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 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
Aug 29, 2007
is there a property in the sql server where i can track the length of the value in a field.
Funnyfrog
View 4 Replies
View Related
May 1, 2007
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 ;)
Thanks later, and in advance :p
-GeorgeV
View 14 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
Nov 21, 2000
What is the max field length in SQL Server 7.0 that a varchar field can be?
I think 8000. Please advise
Thanks
View 2 Replies
View Related
Sep 28, 2001
Hi, can anyone tell me the best/easiest way to increase a field length in a table. We are using SQL 6.5
Regards
View 2 Replies
View Related
Dec 1, 2004
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.
Any ideas?
Thanks!
View 1 Replies
View Related
Dec 31, 2012
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.
View 2 Replies
View Related
Sep 21, 2007
I'm sure it is possible to select all records where the length of a given field equals a certain value, but I don't know how to do it?
Can somebody tell me how?
Hans
View 4 Replies
View Related
Dec 5, 2015
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.
And this is my query
SELECT
GETDATE() As [abc cde fghijkllmalama aljkjlkj alkjkljlkjkljk aljkljlkljk aljkascnalk alkjasdlkjlkajsd asdljklkasdjflkjasdf alkjasqquiouoie oiuoiuoqq aoiuou!]
View 3 Replies
View Related
Jun 9, 2015
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.
View 9 Replies
View Related
May 20, 2015
I have a field in a table
FormID nvarchar(6)
i want to change the length of the datatype to nvarchar(8).
what is the best way to do with out dropping the table?
View 5 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
Sep 29, 2006
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?
View 1 Replies
View Related
May 26, 2000
hai guys,
i want to find out the length of the varbinary field ex: 0x000003752B226B3D0579
thanks
hari
View 1 Replies
View Related
Sep 25, 2000
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!!
View 2 Replies
View Related
Feb 2, 2005
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
View 5 Replies
View Related
Dec 7, 1998
Hi,
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...
Will appreciate any suggestions.
Thanks,
Nishi
View 4 Replies
View Related
Jun 15, 2001
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.
View 1 Replies
View Related
