Substring question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Substring question

Author

Topic

Nubututu
Starting Member

4 Posts

Posted - 2012-08-14 : 10:03:20

Firstly Hello, just joined the forum...

down to business..

I have problem in that a column (called notes )in a table (called dtoc) holds a load of notes, all concatinated together in one long list. I would like to know if it is possible to split the column out so each note has its own column. The notes are separated by a marker = the date in ddmmyyy then the text'admin note' the the note, then the date ddmmyyy and the admin note text once more..
I have tried using substring but got lost
would anyone be able to help at all
thankyou
Dan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:18:13

are date values same? or is it varying dates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nubututu
Starting Member

4 Posts

Posted - 2012-08-14 : 10:26:24

Hello, the dates are different as it will a note field updated every day or few days. here is an example :
14 08 2012 admin Note, No staff. Released! 14 08 2012 admin note, No staff. Still waiting 14 08 2012 admin note, No staff. This person is awaiting placement at a nursing home and is delayed because the bed is not available

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:49:22

ok..so what should be output? everything except admin Note and date values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nubututu
Starting Member

4 Posts

Posted - 2012-08-14 : 10:55:01

it can include all of that stuff really. It would be ideal to have each column looking like this :
14 08 2012 admin Note, No staff. Released!
14 08 2012 admin note, No staff. Still waiting
14 08 2012 admin note, No staff. This person is awaiting placement at a nursing home and is delayed because the bed is not available

but the only thing that is constant that determines the split between notes is the text: 'admin note'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 11:43:44

then use this logic

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

and call it like


SELECT f.Val
FROM yourtable t
CROSS APPLY dbo.ParseValues(t.notes,'admin Note')f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-08-14 : 19:57:25

Have you considered normalizing the data and does every record have the same number of notes?

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

Nubututu
Starting Member

4 Posts

Posted - 2012-08-15 : 04:58:41

thankyou for your help. we have had a play with it and are almost there (to understanding it and making it work for us ! )
cheers dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources