Count dots '.'

Question

Post reply

Count dots '.'

davdam8

Hall of Fame

Points: 3620
More actions
March 22, 2013 at 9:52 am

#275534

Hi geniuses,
How do I Count the number of '.' (dots) from a column value:
EconSearch
D.01.01.01
D.01.01.02
D.01.01.03
D.01.01.03.00
And if there's 3 '.' -> Level 1
if there's 4 '.' -> Level 2
if there's 4 '.' -> Level 3
to Level 5
Thanxs people! Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

;with EconSearch(SomeValue) as

(

select 'D.01.01.01' union all

select 'D.01.01.02' union all

select 'D.01.01.03' union all

select 'D.01.01.03.00'

)

select len(SomeValue) - len(Replace(SomeValue, '.', '')) as NumberOfPeriods

from EconSearch

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

Thanks!

Based on this query and this example (http://www.sqlservercentral.com/Forums/Topic1416551-391-1.aspx), which only retrieves only four levels:

With MySampleData (AllLevels)

AS

(

SELECT 'Analysis»Letters»Numbers»Detail' UNION ALL

SELECT 'Description»Letters»Numbers»Detail' UNION ALL

SELECT 'Planning»Letters»Symbols»Detail'

)

SELECT

PARSENAME(REPLACE(AllLevels,'»','.'),4) As Level1,

PARSENAME(REPLACE(AllLevels,'»','.'),3) As Level2,

PARSENAME(REPLACE(AllLevels,'»','.'),2) As Level3,

PARSENAME(REPLACE(AllLevels,'»','.'),1) As Level4

FROM MySampleData

How do I do if there's 8 levels?

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

davdam8 (3/22/2013)
Thanks!
Based on this query and this example (http://www.sqlservercentral.com/Forums/Topic1416551-391-1.aspx), which only retrieves only four levels:
With MySampleData (AllLevels)
AS
(
SELECT 'Analysis»Letters»Numbers»Detail' UNION ALL
SELECT 'Description»Letters»Numbers»Detail' UNION ALL
SELECT 'Planning»Letters»Symbols»Detail'
)
SELECT
PARSENAME(REPLACE(AllLevels,'»','.'),4) As Level1,
PARSENAME(REPLACE(AllLevels,'»','.'),3) As Level2,
PARSENAME(REPLACE(AllLevels,'»','.'),2) As Level3,
PARSENAME(REPLACE(AllLevels,'»','.'),1) As Level4
FROM MySampleData
How do I do if there's 8 levels?
Thanks

I changed up the sample so there were more than 4 levels. PARSENAME will only work up to 4 levels. You need to parse your string first. You can do this by using the string splitter found by following the link in my signature about splitting strings.

;With MySampleData (AllLevels)

AS

(

SELECT 'Analysis.Letters.Numbers.Detail.Description.Planning.Symbols'

)

SELECT Item, ItemNumber as Level

FROM MySampleData

cross apply dbo.DelimitedSplit8K(AllLevels, '.') x

Now in order for you to get this back into columns you will need to do a cross tab. Depending on your data this can be a fixed number of columns or dynamic. The articles in my signature will walk you through the process of turning the above result set into a cross tab.

Hopefully this will help get you started down a path to a solution. It seems there are a number of details that you have not shared so I did not write up a complete solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

davdam8 Hall of Fame Points: 3620 More actions · Answer 4

Thanks Sean,

this is really helpfull.

However all the specification you gave are very terrifying, i'm only a rookie.

The work i'm in now requires the separation of the data into levels, like the previous example but this time we have 5 levels.

The data goes like this:

;with EconSearch(SomeValue) as

(

select 'D.01.01.01' union all

select 'D.01.01.02' union all

select 'D.01.01.03' union all

select 'D.01.01.03.00' union all

select 'D.01.01.03.00.09'

)

where in the last case: 'D.01.01.03.00.09'

we need a way to retreive something like:

Level Value

101

201

303

400

509

Thanks in advance.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

davdam8 (3/22/2013)
Thanks Sean,
this is really helpfull.
However all the specification you gave are very terrifying, i'm only a rookie.
The work i'm in now requires the separation of the data into levels, like the previous example but this time we have 5 levels.
The data goes like this:
;with EconSearch(SomeValue) as
(
select 'D.01.01.01' union all
select 'D.01.01.02' union all
select 'D.01.01.03' union all
select 'D.01.01.03.00' union all
select 'D.01.01.03.00.09'
)
where in the last case: 'D.01.01.03.00.09'
we need a way to retreive something like:
Level Value
101
201
303
400
509
Thanks in advance.

Sean has given you the pieces you need, the code in his post and the links in his signature block. Give it a shot and if you have problems, come back and show what you have done and where you are having a problem.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Cool. Sounds like you don't need the cross tab stuff at all. 😛

The following will produce the results you are looking for.

;with EconSearch(SomeValue) as

(

select 'D.01.01.01' union all

select 'D.01.01.02' union all

select 'D.01.01.03' union all

select 'D.01.01.03.00' union all

select 'D.01.01.03.00.09'

)

SELECT Item, ItemNumber - 1 as Level

FROM EconSearch

cross apply dbo.DelimitedSplit8K(SomeValue, '.') x

where SomeValue = 'D.01.01.03.00.09'

and ItemNumber > 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Make sure you don't just blindly find that function and use it. You need to read that article and understand what it is doing!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/