Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help with a query !

Author  Topic 

sqlilliterate
Starting Member

40 Posts

Posted - 2008-08-24 : 07:34:44

Help me with a query to get my required output...

In the following text, i want the 3 words after the hyphen in the 1st qry, and 5 words after the hyphen in the 2nd qry.

'Noise is suppresed - at high constrast edges and images appears - sharp and clear'

The output should look like :

at high constrast
at high constrast edges and


thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-24 : 13:43:09
create a udf as follows:-

CREATE FUNCTION ParseValues
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val int
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END


then use it like this
1.
SELECT Val FROM dbo.ParseValues(REPLACE(SUBSTRING(yourtext,CHARINDEX('-',yourtext)+2,LEN(yourtext)),' ',','))t
WHERE t.ID<=3


2.
SELECT Val FROM dbo.ParseValues(REPLACE(SUBSTRING(yourtext,CHARINDEX('-',yourtext)+2,LEN(yourtext)),' ',','))t
WHERE t.ID<=5
Go to Top of Page
   

- Advertisement -