sql parse - 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

sql parse

Author

Topic

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-23 : 11:51:40

I need a query that will parse out a number from a string

so my results are as follows:

11.3.5.6
5.6.4
3.5.7
11.3.4
11.33.3
5.3
#33
#34

so i need a query that will show

number count
11 3
5 2
3 1
other 2

so there are 3 results that start with 11, 2 that start with 5 and 1 that starts with 3. The "other" column will be anything that does not follow the regular naming convention. basically it does not have a "." as a delimiter.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-23 : 12:07:47

Since the count of "." are not consistent in the data, I feel the Parse function cannot be used.

Alternate way to satify your requirement. Try and let us know.

Select OutVal,count(*) from
(
Select case when Charindex('.',<columnname>) > 1 then left(sval,Charindex('.',<columnname>) -1)
else <columnname> end as OutVal from <TableName>
where sval like '[0-9]%'
) as subtab
group by outval

Sample example for your understanding:

Declare @Sample table
(
SVal varchar(50)
)

Insert into @Sample
select '11.3.5.6' union
select '5.6.4' union
select '3.5.7' union
select '11.3.4' union
select '11.33.3' union
select '5.3' union
select '#33' union
select '#34'

--select * from @Sample

Select OutVal,count(*) from
(
Select case when Charindex('.',sval) > 1 then left(sval,Charindex('.',sval) -1)
else sval end as OutVal from @sample
where sval like '[0-9]%'
) as subtab
group by outval

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:16:57

quote:
Originally posted by arusu

I need a query that will parse out a number from a string

so my results are as follows:

11.3.5.6
5.6.4
3.5.7
11.3.4
11.33.3
5.3
#33
#34

so i need a query that will show

number count
11 3
5 2
3 1
other 2

so there are 3 results that start with 11, 2 that start with 5 and 1 that starts with 3. The "other" column will be anything that does not follow the regular naming convention. basically it does not have a "." as a delimiter.


SELECT Header,
COUNT(Field) AS Count
FROM
(
SELECT CASE WHEN field LIKE '[0-9]%' THEN LEFT(Field,CASE WHEN CHARINDEX('.',Field) > 0 THEN CHARINDEX('.',Field)-1 ELSE LEN(Field) END) ELSE 'Other' END AS Header,
Field
FROM YourTable
)t
GROUP BY Header

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

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-23 : 12:37:23

Ok I thought I could figure out what I need from your answers which i do appreciate but there is more and this is more complicated than what I thought.

I have another table which i need to join with results for each category
so the new table will have

number color
11.1.1 red
11.3.5 red
11.3.2 blue

so the query that I need that had

number count
11 3

will now need to be

number red blue yellow
11 2 1 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 12:59:03

you can use same logic in your second table also to get categorywise totals.

like


SELECT Header,
COUNT(CASE WHEN Color='red' THEN 1 ELSE NULL END) AS Red,
COUNT(CASE WHEN Color='blue' THEN 1 ELSE NULL END) AS Blue,
COUNT(CASE WHEN Color='yellow' THEN 1 ELSE NULL END) AS Yellow
FROM
(
SELECT CASE WHEN field LIKE '[0-9]%' THEN LEFT(Field,CASE WHEN CHARINDEX('.',Field) > 0 THEN CHARINDEX('.',Field)-1 ELSE LEN(Field) END) ELSE 'Other' END AS Header,
Color
FROM YourTable
)t
GROUP BY Header

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

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-23 : 13:25:54

but my second table which has the number and color, needs to join from the table that just has the numbers because there are some numbers from the table with color that do not exist in the first table with just the number

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:30:51

ok then do like


SELECT q1.Header,
q1.[Count],
q2.Red,
q2.Blue,
q2.Yellow
FROM (First query)q1
LEFT JOIN (Second query)q2
ON q2.Header = q1.Header

put queries in right places

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

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-23 : 15:53:21

How do I get the percentages of the each color's count as another column
so:

number red yellow blue %red %yellow %blue
11 2 0 2 50.0% 0.0% 50.0%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:20:39

make second query as


SELECT Header,
COUNT(CASE WHEN Color='red' THEN 1 ELSE NULL END) AS Red,
COUNT(CASE WHEN Color='blue' THEN 1 ELSE NULL END) AS Blue,
COUNT(CASE WHEN Color='yellow' THEN 1 ELSE NULL END) AS Yellow,
COUNT(CASE WHEN Color='red' THEN 1 ELSE NULL END)*100.0/COUNT(*) AS [%red],
COUNT(CASE WHEN Color='yellow' THEN 1 ELSE NULL END)*100.0/COUNT(*) AS [%yellow],
COUNT(CASE WHEN Color='blue' THEN 1 ELSE NULL END)*100.0/COUNT(*) AS [%blue]
FROM
(
SELECT CASE WHEN field LIKE '[0-9]%' THEN LEFT(Field,CASE WHEN CHARINDEX('.',Field) > 0 THEN CHARINDEX('.',Field)-1 ELSE LEN(Field) END) ELSE 'Other' END AS Header,
Color
FROM YourTable
)t
GROUP BY Header

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

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 10:01:40

Hi, thank you for all your help so far, i think i just have one more question

what if i want to get the percentage of red between red and blue? and how do i get the percentage to show only to one decimal place?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 10:08:01

for that just include counts of red and blue alone in denominator rather than count(*). a small change in last suggestion will do it. I'm leaving it to you to try it yourself. dont want to spoonfeed you.
you can use ROUND() function for that.

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

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 10:20:50

sorry i did not mean for you to feel like you are spoon feeding me. When i ask a question, i have already tried a solution and it was not working for me. For example i already tried

COUNT(CASE WHEN color != 'yellow' THEN 1 ELSE NULL END) on the denominator

but got this error

Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

and i already knew of the round function and used it like this:

ROUND(COUNT(CASE WHEN Color='red' THEN 1 ELSE NULL END)*100.0/COUNT(*), 1) AS [%red]

but it did not work.

You don't have to give me the answer, and i feel bad to ask but maybe you have a hint?

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 10:44:36

ok so the round works with a convert to decimal but i am still having the first problem

any suggestions?

arusu
Yak Posting Veteran

60 Posts

Posted - 2010-08-24 : 11:30:24

I figured it out. I am using an ifnull on the denominator and a isnull on the entire thing to turn it into a zero

thanks for the help from before

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:40:49

quote:
Originally posted by arusu

I figured it out. I am using an ifnull on the denominator and a isnull on the entire thing to turn it into a zero

thanks for the help from before

i hope you meant NULLIF
yeah..thats the way to go

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources