T-SQL functions to convert Strings to Tables
- Joel Lipman
- Transact-SQL
- Hits: 7198
- 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...
This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which...
What? This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on...
Applies to: MS SQL Server Management Studio 2008 Two Transact-SQL databases What? A quick article on how to compare two...
What? Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to...
What? Some people have been suggesting you can use "CREATE OR REPLACE... VIEW... FUNCTION" but my SQL Server 2008...
name version page display report uploaded mysql deluge used case date system time server form value file following note would parameter order using creator table website work zoho license script create function data code joomla record list need error client files find field added first where user windows database source JoelLipman.Com