Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

display specific string and that positions in sql server Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 9:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 210, Visits: 4,520
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.
Post #1448296
Posted Tuesday, April 30, 2013 9:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1448298
Posted Thursday, May 2, 2013 2:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Name posnum pos
i am good in sas 1 4
i am sas programmer 1 4
i am sas programmer 2 16
i am sas programmer 3 17
mms programmer 1 1
mms programmer 2 2
mms programmer 3 11
mms programmer 4 12
my name is ravikumar 1 1
my name is ravikumar 2 6
my name is ravikumar 3 18
sas programmer 1 11
sas programmer 2 12


Post #1448981
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse