T-SQL functions to convert Strings to Tables
- Joel Lipman
- Transact-SQL
- Hits: 6787
- Microsoft SQL Server 2008 R2
- Microsoft SQL Server 2012
These were in a solution and I thought I'd note them on my site so I can refer to them more easily.
How?
SELECT EmployeeNo , DaysOffSick , DateOfSickness FROM Employees_Attendance_Table -- yields EmployeeNo DaysOffSick DateOfSickness -------------- -------------- ---------------- 001 1.00 1997-11-17 002 3.00 2000-02-18 003 2.00 1999-02-25
EmployeeNo DaysOffSick DateOfSickness -------------- -------------- ---------------- 001 1.00 1997-11-17 002 1.00 2000-02-18 002 1.00 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 -- note the dates increment and do not account for days off (eg. Saturday / Sunday)
String1.String2.String3.String4 1 5 10 15 20 25 30 -> length = 31
Before: StudentID StudentName DateOfBirth ----------- -------------------- ------------- 1 John Smith 1990-03-21 2 Fred Bloggs 1988-11-02 3 Another User 1985-07-11 4 Yet Another User 1977-06-25 After: StudentID StudentName DateOfBirth ----------- -------------------- ------------- 1 PDUHjRWJcb 1926-01-02 2 WRmNqQKxvuV 1969-03-14 3 nBCkAVDrvdhe 1968-05-05 4 RJDsFMaeNcLrcMWw 1964-08-08
ID Name DateOfBirth --------- ------------ ----------- 1 John Smith 1988-06-24 2 Fred Bloggs 1972-11-17 3 Another User 1964-02-18
ID Name DateOfBirth --------- ------------ ----------- 1 Fred Bloggs 1964-02-18 2 Another User 1988-06-24 3 John Smith 1972-11-17
Thought I'd put a note on this error. Bearing in mind that this is a general data type error and not just because I...
What? A very quick note in case I forget this one. If you are trying to join two tables and receiving the error...
Applies to Transact-SQL (T-SQL) What? This is a quick article on how to split a single row into multipe rows based on...
What? So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a...
Well I find myself again the dummie of the Internet. I basically came across this error and STFW'd for ages following...
What? This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and...
Why? Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want...
would windows note system value where function report table page website form database server display name error using following date mysql added script case used first parameter work code time creator client field create files data find zoho deluge need uploaded google file list joomla user license source order version JoelLipman.Com