SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


display specific string and that positions in sql server


display specific string and that positions in sql server

Author
Message
asranantha
asranantha
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 4717
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.
Calibear
Calibear
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 Visits: 1721
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search