Count number within string - 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

General SQL Server Forums

New to SQL Server Programming

Count number within string

Author

Topic

SQLforum
Starting Member

4 Posts

Posted - 2011-01-24 : 16:00:30

Dear All,

I am trying to create buckets using a string column. This string column contain 12 digits of number from 0-9. ex: 001211121003, 321010987642.

Here is my condition:

If the "1" show up twice in "DQ_string" then all other digits within the string must be < 1.
or
If the "2" show up 1 time then all other digits within "DQ_string" must be < 2

Please advise. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-24 : 16:12:22

This type of code should be done in your application. Return the raw data from SQL and then do your string checking in the application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SQLforum
Starting Member

4 Posts

Posted - 2011-01-24 : 16:20:29

Tara, thanks for your reply but I need to write this up myself. "DQ_tring" is the column name. number in that string indicate its payment strength for 12 months period. "1" meaning 30 late or "2" will be 60 days late etc. I need to find out within that string how many times the 1 or 2 appear but never more than 2 within that string. Can someone help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-24 : 17:21:56

SELECT CASE
WHEN DQ_String LIKE '%2%2%' THEN 'Total Deadbeat'
WHEN DQ_String LIKE '%2%' THEN '60 Days Late'
WHEN DQ_String LIKE '%1%1%' THEN 'Deadbeat'
WHEN DQ_String LIKE '%1%' THEN '30 Days Late'
ELSE 'Not Late' END AS PaymentHistory
FROM myTable

SQLforum
Starting Member

4 Posts

Posted - 2011-01-24 : 17:53:04

Thanks Robvolk, However I only need to see 1 or 2 twice within that string and the rest of the number in that string will be less than that number. ex: 01100000000, or 010010000000 or 0200010000, or 02000200000 etc. yours giving me 1 or 2 more than twice plus number > 2 or 1.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-24 : 18:29:34

Just remove the WHEN conditions you don't need from the expression.

The example I gave doesn't check that the other digits are all zero, but from what you've described it doesn't seem necessary if they meet either of the other conditions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources