CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Karthi
Karthi

Reputation: 708

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 
Company:CamTel 
Business Phone: 1234567890 
Business Email: [email protected] 
Device Model: SGH-I547C 
OS version: 4.1.2 
Application Version: 1.0.59.2 
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.

Any suggestion would be greatly helpful.

User InformationName | Company | Business Phone  |Email                        |  Device Model |  OS version | Application Version | Product Name
jweller                CamTel    1234567890      [email protected]   SGH-I547C         4.1.2      1.0.59.2              Demo Mobile Android App

Upvotes: 1

Views: 99

Answers (2)

James Z
James Z

Reputation: 12318

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

Upvotes: 1

Aleksandar Miladinovic
Aleksandar Miladinovic

Reputation: 1027

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...

GL!

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...

Upvotes: 1

Related Questions