string operation

Question

Post reply

string operation

BeginnerBug

Hall of Fame

Points: 3539
More actions
September 25, 2012 at 6:05 am

#282177

hi all,

i have the word 'tapsaw1'

can i get the result as follows by not tusing while loop?

tapsaw1

tapsaw

tapsa

taps

tap

ta

t

apsaw1

apsaw

apsa

aps

ap

a

psaw1

..

..

Like this?

thanks in advance

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

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

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

Use a numbers table



DECLARE @S VARCHAR(10)

SET @S = 'tapsaw1'

SELECT SUBSTRING(@s,t2.number,t1.number)

FROM master.dbo.spt_values t1

INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND LEN(@s) - t1.number + 1

WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s)

ORDER BY t2.number ,t1.number DESC

Fixed...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

Mark-101232 (9/25/2012)

Use a numbers table

DECLARE @S VARCHAR(10) SET @S = 'tapsaw1' SELECT SUBSTRING(@s,t2.number,t1.number) FROM master.dbo.spt_values t1 INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND t1.number-1 WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s) ORDER BY t2.number ,t1.number DESC

That doesn't work for me:

----------

tapsaw1

tapsaw

tapsa

taps

tap

ta

apsaw1

apsaw

apsa

aps

psaw1

psaw

saw1

aw1

w1

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

This does though, with small adjustments:



DECLARE @S VARCHAR(10)

SET @S = 'tapsaw1'

SELECT SUBSTRING(@s,t2.number,t1.number-(t2.number-1))--,t2.number,t1.number-(t2.number-1)

FROM master.dbo.spt_values t1

INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND t1.number

WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s)

ORDER BY t2.number ,t1.number DESC

----------

tapsaw1

tapsaw

tapsa

taps

tap

ta

t

apsaw1

apsaw

apsa

aps

ap

a

psaw1

psaw

psa

ps

p

saw1

saw

sa

s

aw1

aw

a

w1

w

1

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

I made a different adjustment and seems easier to read (at least for me :-D)



SELECT SUBSTRING(@s,t2.number,t1.number)

FROM master.dbo.spt_values t1

INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND LEN(@s)

WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s) - t2.number + 1

ORDER BY t2.number ,t1.number DESC

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 5

Luis Cazares (9/25/2012)

I made a different adjustment and seems easier to read (at least for me :-D)

SELECT SUBSTRING(@s,t2.number,t1.number) FROM master.dbo.spt_values t1 INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND LEN(@s) WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s) - t2.number + 1 ORDER BY t2.number ,t1.number DESC

which is almost identical to the fix I put in

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Luis Cazares SSC Guru Points: 183686 More actions · Answer 6

Mark-101232 (9/25/2012)

Luis Cazares (9/25/2012)

I made a different adjustment and seems easier to read (at least for me :-D)

SELECT SUBSTRING(@s,t2.number,t1.number) FROM master.dbo.spt_values t1 INNER JOIN master.dbo.spt_values t2 ON t2.type='P' AND t2.number BETWEEN 1 AND LEN(@s) WHERE t1.type='P' AND t1.number BETWEEN 1 AND LEN(@s) - t2.number + 1 ORDER BY t2.number ,t1.number DESC

which is almost identical to the fix I put in

:w00t: I didn't see it before 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86913 More actions · Answer 7

Just for fun and to show that there's always more than one way to skin a cat:



DECLARE @S VARCHAR(10) = 'tapsaw1'

;WITH rCTE (n, s) AS (

    SELECT LEN(@s), @S

    UNION ALL

    SELECT LEN(s) - 1, SUBSTRING(s, 2, LEN(s) - 1)

    FROM rCTE

    WHERE LEN(s) > 1

    UNION ALL

    SELECT LEN(s) - 1, SUBSTRING(s, 1, LEN(s) - 1)

    FROM rCTE

    WHERE LEN(s) > 1)

SELECT DISTINCT s

FROM rCTE

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St