sql查询以查找特殊字符之间的字符串 [英] sql query to find string between special Characters

问题描述

我有一些数据库的名为地址"的列，其中的数据是这样的...

-``sanjeevanagar，5th Road 5-34，D.No. |城市名| Pincode |州|国家/地区"
-``dgfkj gdkh fghk | CityName | pincode | state | country''

我想获取CityName以将其用作参数...请建议查询...预先输入谢谢

Hi,
I have a column named ''Address'' of some database, in which Data is something like this...

-- ''D.No 5-34,5th Road,sanjeevanagar|Cityname|Pincode|state|Country''
-- ''dgfkj gdkh fghk|CityName|pincode|state|country''

i want to fetch CityName to use it as a parameter ...please suggest the query...thanx in advance

推荐答案

这里是函数，只需复制，粘贴享受..

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

谢谢
--RA

Thanks
--RA

这是我为您准备的程序，我早就编写了该程序.将其更改为功能并在您的select语句中使用

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

谢谢
--RA

Thanks
--RA

这篇关于sql查询以查找特殊字符之间的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！