SQL Query data splitting

I need a help in processing a column to the output as show below in sql. I have a column called Description which has the following text.

User InformationName:jweller 
Business Phone: 1234567890 
Business Email: mobiledevsupport@Demo.com 
Device Model: SGH-I547C 
OS version: 4.1.2 
Application Version: 
Product Name: Demo Mobile 
Android App Device language: English (Canada) Device 
Time Zone: Mountain Standard Time 
Data connection:Cellular network (3G) 
Signal Strength: -81dBM  
Error Message Displayed to the U

I want this text to be converted in row ,columns and something like this. I am using sql server 2008.

  • Count times ID appears in a table and return in row
  • SQL Server 2000 + ASP.NET: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
  • MS Access holds locks on table rows indefinitely
  • SQL DDL to support migration and updates
  • Fastest way to sort in Ms sql
  • SQL getting multiple columns into one column
  • Any suggestion would be greatly helpful.

    User InformationName | Company | Business Phone  |Email                        |  Device Model |  OS version | Application Version | Product Name
    jweller                CamTel    1234567890      mobiledevsupport@Demo.com   SGH-I547C         4.1.2              Demo Mobile Android App

    2 Solutions collect form web for “SQL Query data splitting”

    to get this result you could try something like this:

    SELECT SUBSTRING(description, (CHARINDEX('User InformationName:', description) + LEN('User InformationName:')), CHARINDEX('Company:', description) - (CHARINDEX('User InformationName:', description) + LEN('User InformationName:'))) AS [User InformationName],
           SUBSTRING(description, (CHARINDEX('Company:', description) + LEN('Company:')), CHARINDEX('Business Phone:', description) - (CHARINDEX('Company:', description) + LEN('Company:'))) AS [Company],
           SUBSTRING(description, (CHARINDEX('Business Phone:', description) + LEN('Business Phone:')), CHARINDEX('Business Email:', description) - (CHARINDEX('Business Phone:', description) + LEN('Business Phone:'))) AS [Business Phone],
           SUBSTRING(description, (CHARINDEX('Business Email:', description) + LEN('Business Email:')), CHARINDEX('Device Model:', description) - (CHARINDEX('Business Email:', description) + LEN('Business Email:'))) AS [Business Email],
           SUBSTRING(description, (CHARINDEX('Device Model:', description) + LEN('Device Model:')), CHARINDEX('OS version:', description) - (CHARINDEX('Device Model:', description) + LEN('Device Model:'))) AS [Device Model],
           SUBSTRING(description, (CHARINDEX('OS version:', description) + LEN('OS version:')), CHARINDEX('Application Version:', description) - (CHARINDEX('OS version:', description) + LEN('OS version:'))) AS [OS version],
           SUBSTRING(description, (CHARINDEX('Application Version:', description) + LEN('Application Version:')), CHARINDEX('Product Name:', description) - (CHARINDEX('Application Version:', description) + LEN('Application Version:'))) AS [Application Version],
           SUBSTRING(description, (CHARINDEX('Product Name:', description) + LEN('Product Name:')), CHARINDEX('Device language:', description) - (CHARINDEX('Product Name:', description) + LEN('Product Name:'))) AS [Product Name]
    FROM myTable

    Here is SQL Fiddle so you can check is this what are you looking for.

    Here i use simple SUBSTRING function which is explain here and to do calculation from which string index to start and length string I use CHARINDEX and LEN Function, that’s not so complicated you can find there explanation on net very easy…


    P.S. I think I don’t need to say you that this is not very good way to store information and I think it’s obvious why…

    You can also do this using a string splitter, so that you split the data first by newlines into rows, and then separate the values before and after : with something like this:

    select * From (
      select ltrim(left(s.Value, P.P - 1)) as Type, 
             ltrim(substring(s.Value, P.P+1, 9999)) as Value
      from table1 t
      cross apply cte_split_core (t.description, char(10)) s
      cross apply (select charindex(':', s.Value) as P) P
      where P.P > 0
    ) s pivot (
      max(Value) FOR Type IN ([User InformationName],[Company],[Business Phone],[Business Email],[Device Model],[OS version],[Application Version],[Product Name])
    ) Pvt

    The CTE function is from Erland Sommarskog’s article but you can use others too.

    Example in SQL Fiddle

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.