December 17, 2008 at 4:08 am
for example in the string below i want text till the 4th "/"
hello/what/ru/doing?/happy/new year
December 17, 2008 at 4:27 am
Use this code
select left('hello/what/ru/doing?/happy/new year
',len('hello/what/ru/doing?/happy/new year
')-(len('hello/what/ru/doing?/happy/new year
')-charindex('/','hello/what/ru/doing?/happy/new year
')))
December 17, 2008 at 4:36 am
sorry .
when i ran this query its giving me result as 'hello/'
and expected result is hello/what/ru/doing?/
December 17, 2008 at 4:38 am
try this:
DECLARE @string VARCHAR(100)
SET @string = 'hello/what/ru/doing?/happy/new year'
DECLARE @Result VARCHAR(100)
;WITH MyCTE
AS
(SELECT TOP 4 SUBSTRING(@String+'/', n,
CHARINDEX('/', @String+'/', n) - n) as Val
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING('/' + @String,
n, 1) = '/'
ORDER BY n )
SELECT @Result = ISNULL(@Result,'') + ' ' + Val FROM MyCTE
SELECT @Result
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 4:49 am
thank you.
but its giving the below error
Invalid object name 'tally'.
December 17, 2008 at 4:55 am
you need to create the tally table
SELECT TOP 11000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 5:03 am
thanks again Christopher Stobbs
its working and result is ---- hello what ru doing?
you have removed all the '/' but i need them and my expected result is
-- hello/what/ru/doing?/
December 17, 2008 at 5:06 am
cool all you need to do is change the concatenation in the last select
DECLARE @string VARCHAR(100)
SET @string = 'hello/what/ru/doing?/happy/new year'
DECLARE @Result VARCHAR(100)
;WITH MyCTE
AS
(SELECT TOP 4 SUBSTRING(@String+'/', n,
CHARINDEX('/', @String+'/', n) - n) as Val
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING('/' + @String,
n, 1) = '/'
ORDER BY n )
SELECT @Result = ISNULL(@Result,'') + Val + '/' FROM MyCTE
SELECT @Result
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 5:16 am
thanks christopher !!
thats great work!!! but i need to use this query else where
and i dont want to create that table(tally ).
Is there any way around for doing this
December 17, 2008 at 5:22 am
Why don't you want to create the table?
Having a tally table is very useful...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 5:23 am
try this:
DECLARE @string VARCHAR(100)
SET @string = 'hello/what/ru/doing?/happy/new year'
DECLARE @Result VARCHAR(100)
;WITH MyCTE
AS
(SELECT TOP 4 SUBSTRING(@String+'/', n,
CHARINDEX('/', @String+'/', n) - n) as Val
FROM
(SELECT TOp 100 ROW_NUMBER() OVER (PARTITION BY GETDATE() ORDER BY GETDATE()) as n
FROM Master.dbo.SysColumns) nest
WHERE n <= LEN(@String)
AND SUBSTRING('/' + @String,
n, 1) = '/'
ORDER BY n )
SELECT @Result = ISNULL(@Result,'') + Val + '/' FROM MyCTE
SELECT @Result
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 17, 2008 at 5:29 am
working fine. many thanks
January 15, 2011 at 12:04 am
See this article related to same
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply