Neat SELECT? 1

[Naoise]

I have a table as follows :

Foo | Position
a 1
b 1.1
c 1.1.1
d 1.2
e 1.2.1
f 1.2.1.1
g 1.2.1.2
h 1.2.1.3
i 1.2.1.3.1

I want to SELECT from this table but the recordset would already achieve my indentation for me. So each digit in the Position field will be replaced by ' ' ie

Foo
 a
  b
 &nbsp c

Easy to add the two columns together and remove the dots but hard to do a regex then on Position to replace digits with   without a lot of hassle. Any ideas?

 

[ptheriault]

You could try using the replace() function with a case statement that uses isnumeric. Something like if the value is a number replace it with &nbsp if it is a '.' then replace it with a ';'.

What have you got so far?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Here is an idea:

Basically, you want to REPLICATE (see BOL) your character string for each non-period character in the position column.

[COLOR=blue]declare[/color] @foo [COLOR=blue]table[/color] (let [COLOR=blue]char[/color](1), [COLOR=blue]position[/color] [COLOR=blue]varchar[/color](15))

[COLOR=blue]insert[/color] @foo
[COLOR=blue]select[/color] [COLOR=red]'a'[/color], [COLOR=red]'1'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'b'[/color], [COLOR=red]'1.1'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'c'[/color], [COLOR=red]'1.1.1'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'d'[/color], [COLOR=red]'1.2'[/color]

[COLOR=blue]select[/color] [COLOR=#FF00FF]replicate[/color]([COLOR=red]' '[/color], len([COLOR=#FF00FF]replace[/color]([COLOR=blue]position[/color], [COLOR=red]'.'[/color], [COLOR=red]''[/color]))) + let
[COLOR=blue]from[/color] @foo

Hope it helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[Naoise]

I don't have any one line SELECT solution, I've been looking at UDF's for the past while.

I just had the obvious

SELECT Replace(Position,'.','') + Foo FROM tblFooBar and was waiting to see what if anything could be wrapped around the first Replace to change each digit to  

 

[Naoise]

That is it to a T. Thanks very much

 

[ptheriault]

Nice solution Alex.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

Glad you got it working

And thanks Paul!

Ignorance of certain subjects is a great part of wisdom