sql query to find string between special Characters

Question

0.00/5 (No votes)

See more:

Posted 19-Mar-12 4:50am

DileepkumarReddy

Updated 19-Mar-12 4:51am

v2

Add a Solution

2 solutions

Solution 1

Here is my procedure for you, I have written this procedure long back. change it as function and use in your select statement

create PROCEDURE [dbo].[SplitGETPos](
@String varchar(8000),
@Delimiter char(1),
@POSITION INT,
@OUTPUT VARCHAR(100) OUTPUT)    
WITH
EXECUTE AS CALLER
as    
begin    
    declare @idx int;    
    DECLARE @CHARIDX INT;
    DECLARE @INPUTSTR VARCHAR(8000);
    SET @INPUTSTR=@String;
    SET @CHARIDX =0;
    SET @idx=0;
    while @idx < @POSITION  
    begin    
          SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
          SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
          SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
        SET @idx=@idx+1;
    end
return    
end

Thanks
--RA

Posted 19-Mar-12 4:55am

Rajesh Anuhya

Updated 19-Mar-12 4:59am

v2

Comments

DileepkumarReddy 19-Mar-12 10:57am

Thank you so much...i will check it out..

DileepkumarReddy 19-Mar-12 11:03am

Hi, what is Position and Output parameters here...i dint understand...

what do i need to pass in them..Please explain

Rajesh Anuhya 19-Mar-12 11:06am

Position mean:
========================
Rajesh|Ramesh|Rakesh
1 2 3
=========================
1,2,3 are the positions

I am returning the value in output parameters, just change this procedure to function(which returns the output value)
--RA

DileepkumarReddy 19-Mar-12 11:19am

create function [dbo].[SplitGETPos](
@String varchar(8000),
@Delimiter char(1),
@POSITION INT,
@OUTPUT VARCHAR(100) OUTPUT)
--WITH
Returns varchar(100)
--EXECUTE AS CALLER
as
begin
declare @idx int;
DECLARE @CHARIDX INT;
DECLARE @INPUTSTR VARCHAR(8000);
SET @INPUTSTR=@String;
SET @CHARIDX =0;
SET @idx=0;
while @idx < @POSITION
begin
SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
SET @idx=@idx+1;
return @OUTPUT
end

Msg 181, Level 15, State 1, Procedure SplitGETPos, Line 5
Cannot use the OUTPUT option in a DECLARE or CREATE FUNCTION statement.
Msg 137, Level 15, State 1, Procedure SplitGETPos, Line 20
Must declare the scalar variable "@OUTPUT".
Msg 137, Level 15, State 2, Procedure SplitGETPos, Line 25
Must declare the scalar variable "@OUTPUT".

unable to create function...help me out

Rajesh Anuhya 19-Mar-12 11:23am

See my next answer
--RA

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh Anuhya · Accepted Answer · 2012-03-19T05:23:00

Here is the Function, Just copy, paste and enjoy..

create function [dbo].[SplitGETPos](
@String varchar(1000), 
@Delimiter char(1),
@POSITION INT) 
returns varchar(20)
as     
begin     
    declare @OUTPUT varchar(20);
    declare @idx int;     
    DECLARE @CHARIDX INT;
    DECLARE @INPUTSTR VARCHAR(1000);
    SET @INPUTSTR=@String + ',';
    SET @CHARIDX =0;
    SET @idx=0;
    while @idx < @POSITION   
    begin     
          SET @CHARIDX= CHARINDEX(@Delimiter, @INPUTSTR);
          SET @OUTPUT=SUBSTRING(@INPUTSTR, 1, @CHARIDX - 1)
          SET @INPUTSTR = SUBSTRING(@INPUTSTR,@CHARIDX +1,LEN(@INPUTSTR));
        SET @idx=@idx+1;
    end 
return  @OUTPUT
end

Thanks
--RA