April 30, 2013 at 9:32 pm
hi friend i have small doubt in sql server plz tell me how to solve this
i want display only specific string in based on table data
suppose to disply 'm' string based on table and that position
name
sas programer
mms programer
my name is ravikumar
iam sas programer
iam good in sas
based on this i want output like this
name , string , position
sas programer , m , 11
mms programer , m , 1
mms programer , m , 2
mms programer , m , 11
my name is ravikumar , m , 1
my name is ravikumar , m , 6
my name is ravikumar , m , 18
iam sas programer , m , 3
iam sas programer , m , 15
iam good in sas , m , 3
plz tell me how to wirte query in sql server to solve this issue.
April 30, 2013 at 9:48 pm
asranantha,
Review CharIndex (http://msdn.microsoft.com/en-us/library/ms186323.aspx), this will help you to search for the value you are looking for:
select charindex('m' /*search value*/, 'sas programer ' /*column to search*/)
from SomeTable
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 2, 2013 at 2:48 pm
Here's a function that does exactly what you want:
CREATE FUNCTION [dbo].[itvfFindPos]
(
@strInput VARCHAR(8000)
,@delimiter VARCHAR(5)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b
),
E4(N) AS (SELECT 1 FROM E2 a, E2 b
),
cteTally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
findchar (posnum,pos)
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY t.N) AS posnum
,CHARINDEX(@delimiter,@strInput,t.N) AS pos
FROM
cteTally t
WHERE
(SUBSTRING(@strInput,t.N,1) = @delimiter)
)
SELECT
posnum
,pos
FROM
findchar
WHERE
pos > 0
)
GO
Now create some sample data to test the function:
;WITH
SampleData(Name)
AS
(
SELECT 'sas programmer' UNION ALL
SELECT 'mms programmer' UNION ALL
SELECT 'my name is ravikumar' UNION ALL
SELECT 'i am sas programmer' UNION ALL
SELECT 'i am good in sas'
)
SELECT
s.Name
,t.posnum
,t.pos
FROM
SampleData AS s
CROSS APPLY
dbo.itvfFindPos(s.Name,'m') AS t
ORDER BY
Name
,posnum
,pos
Returns
Nameposnumpos
i am good in sas14
i am sas programmer14
i am sas programmer216
i am sas programmer317
mms programmer11
mms programmer22
mms programmer311
mms programmer412
my name is ravikumar11
my name is ravikumar26
my name is ravikumar318
sas programmer111
sas programmer212
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply