question

Manjula avatar image
Manjula asked

Splitting a string which has column names and data in notes field

Hi ,I need help in writing a fuction in SQL and any help will be appreciated. I have a table with thousands of rows which has a column called Notes containing data like below. it is separated by space and the pattern can be different such as version can come first or last. row 1 Version :30 version_type:print group:books author:SAM row 2 group:books version:33 author:john version_type:print row 3 version:32 author:Mary group: version_type:Print I need to insert into a differant table by splitting the string to differant columns. Version version_type group author 30 Print books sam 32 print Mary 32 print books John
parsingtext
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please provide a sample of 10 rows of data? Does the input include the labels (version, version_type, etc.)? If not, how do you know which field is where?
1 Like 1 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Not knowing much about your data, I've had to assume some things if object_id('tempdb..#NotesTable','U') is not null drop table #NotesTable create table #NotesTable ( Notes varchar(max) ) insert into #NotesTable select 'Version :30 version_type:print group:books author:SAM' insert into #NotesTable select 'group:books version:33 author:john version_type:print' insert into #NotesTable select 'version:32 author:Mary group: version_type :print' insert into #NotesTable select 'version:32 author:Kev' insert into #NotesTable select 'version_type :print version:99 author:Keith group:' insert into #NotesTable select '' select case when patindex('%Version[^_]%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%Version[^_]%', Notes))) = 0 then substring(Notes, charindex(':', Notes, patindex('%Version[^_]%', Notes))+1, len(Notes) ) when patindex('%Version[^_]%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%Version[^_]%', Notes))) > 0 then substring(Notes, charindex(':', Notes, patindex('%Version[^_]%', Notes))+1, charindex(' ', Notes, charindex(':', Notes, patindex('%Version[^_]%', Notes))) - charindex(':', Notes, patindex('%Version[^_]%', Notes)) ) else '' end as [Version], case when patindex('%version_type%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%version_type%', Notes))) = 0 then substring(Notes, charindex(':', Notes, patindex('%version_type%', Notes))+1, len(Notes) ) when patindex('%version_type%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%version_type%', Notes))) > 0 then substring(Notes, charindex(':', Notes, patindex('%version_type%', Notes))+1, charindex(' ', Notes, charindex(':', Notes, patindex('%version_type%', Notes))) - charindex(':', Notes, patindex('%version_type%', Notes)) ) else '' end as Version_type, case when patindex('%group%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%group%', Notes))) = 0 then substring(Notes, charindex(':', Notes, patindex('%group%', Notes))+1, len(Notes) ) when patindex('%group%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%group%', Notes))) > 0 then substring(Notes, charindex(':', Notes, patindex('%group%', Notes))+1, charindex(' ', Notes, charindex(':', Notes, patindex('%group%', Notes))) - charindex(':', Notes, patindex('%group%', Notes)) ) else '' end as [group], case when patindex('%author%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%author%', Notes))) = 0 then substring(Notes, charindex(':', Notes, patindex('%author%', Notes))+1, len(Notes) ) when patindex('%author%', Notes) > 0 and charindex(' ', Notes, charindex(':', Notes, patindex('%author%', Notes))) > 0 then substring(Notes, charindex(':', Notes, patindex('%author%', Notes))+1, charindex(' ', Notes, charindex(':', Notes, patindex('%author%', Notes))) - charindex(':', Notes, patindex('%author%', Notes)) ) else '' end as author from #NotesTable results Version Version_type group author 30 print books SAM 33 print books john 32 print Mary 32 Kev 99 print Keith
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.