question

gurbirrataul avatar image
gurbirrataul asked

Parse a column with the text datatype

Hi guru's,

I m new to Sql Server coding. I have little idea of parsing but i never applied big logic for parsing of the data in column. I have a got a request to count same testresultcode (like 90011, 4/8ratio.... so many) in a day of XYZ type only and every minutes we have so many test results being updated in the table. i have a table with 6 fields in it. out of which one filed is Message with Text datatype. This table gets updated every minute and every row have this kind of data in Message Field:

YES|^~\&|DI|GURB|B2|GURB|20100609173647|| HSN^R01|4349413|P|2.3|30188| PID|1||8045468||MACHINE||19450213|M HSN|NW YEH|1|8045468 XYZ|1||% T-Sum||1.25|||||||||20100609112151 XYZ|2||4/8ratio||1.06|||||||||20100609112151 XYZ|3||90011||7.44|%||||||||20100609112151 XYZ|4||90003||32.09|%||||||||20100609112151 XYZ|5||90009||28.45|%||||||||20100609112151 XYZ|6||CD3+CD4+CD8+%||0.42|%||||||||20100609112151 XYZ|7||90001||63.74|%||||||||20100609112151 XYZ|8||90005||30.39|%||||||||20100609112151 XYZ|9||__||99.63|||||||||20100609112151

Like in the above message, every other row has some message with same or different testcode.

Every minutes a new row is added and i have to find out how a testresultcode like" 90005" has come during the day in all the rows. Guru's please help me ........i promise once i am good in coding i would definitely help others

Thanks

t-sqlqueryuser-defined-function
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
Piotr Rodak avatar image
Piotr Rodak answered

If I may have a question, why the data are not normalized? This looks like a pipe delimited list of rows with some header and footer. If you had a separate column for each field in this string, your life would be so much easier. If you want to count rows that have specific value you can use LIKE keyword, as in

select count(*) from table where XYZ like '%90005%'

If you have situation that you want to count number of occurences of particular substring, you could use for example query based on numbers table

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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.