T-SQL functions to convert Strings to Tables
- Joel Lipman
- Transact-SQL
- Hits: 6266
- 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
This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which...
My setup Windows XP SP3 MS SQL Server Management Studio 2008 SQL Server 2008 R2 Regional Setting: London BST (British...
What? This is a quick article on how to use a counter added to the end of a field column of data. Eh. What? Assume the following...
Thought I'd add a note as I was getting confused with the built-in function "FormatDateTime()". The example is shown...
What? This took me a while to find so I've posted an article below detailing how to parse or extract values from a...
What? So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a...
Applies to Transact-SQL (T-SQL) What? This is a quick article on how to split a single row into multipe rows based on...
joomla client deluge uploaded zoho error google files need website version where name database note used would display form page added value code first creator field source using create following find work order file script system time parameter date table function case data license windows user report mysql server list JoelLipman.Com