August 23, 2008 at 1:22 am
i want to write this in select query how do i write i shud get the same output
declare @test1 varchar(200)
set @test1 = '10919058_PRAVEEN KUMAR_4500.0_36fe78c3ef2daba558338adf8c7d_RNEW_24-08-2008_22-08-2008_02041761'
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)-1)
SET @test1 = substring(@test1, charindex('_',@test1)+1, LEN(@test1))
PRINT substring(@test1 , 1, charindex('_',@test1)+20)
August 23, 2008 at 5:50 am
waiting for the reply.......
August 24, 2008 at 8:03 am
waiting for the reply.......
August 24, 2008 at 8:55 am
You haven't given enough information, such as how this data is stored, nor have you made an attempt to write anything. We typically don't do your work for you. We try to help, but you must make some attempt.
August 24, 2008 at 9:17 am
Read this article; Parsing CSV Values Into Multiple Rows, by Rob Volk
The only difference is that your seperator appears to be an underscore instead of a comma.
Give it a try, if it doesn't work post what you've written.
August 24, 2008 at 9:43 am
Heh... I'm a little biased... please see the following articles on the subject of Tally tables and splits...
http://www.sqlservercentral.com/articles/TSQL/62867/
http://www.sqlservercentral.com/articles/T-SQL/63003/
For the problem at hand, the following works just fine...
First, create the Tally table, like in the article, using the following...
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
Next, do the split using the Tally table... I added a "position" to the output so you know what "column" of the delimited data any given piece of information came from...
--===== Setup the original test data... this is NOT a part of the solution
declare @test1 varchar(200)
set @test1 = '10919058_PRAVEEN KUMAR_4500.0_36fe78c3ef2daba558338adf8c7d_RNEW_24-08-2008_22-08-2008_02041761'
--===== Split or "Normalize" the whole table at once
SELECT ROW_NUMBER() OVER (ORDER BY t.N) AS Element,
SUBSTRING('_'+@test1,N+1,CHARINDEX('_',@test1+'_',N+1)-N) AS Value
FROM dbo.Tally t
WHERE N < LEN('_'+@test1)
AND SUBSTRING('_'+@test1,N,1) = '_'
Of course, you can substitute a CTE for the Tally table (don't use recurrsion, though!) and you can can replace the '_' with a variable to make it all a bit more flexible... most of that is covered in the articles I mentioned above.
Lemme know how it works for you... heh... "waiting for the reply......."
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply