拆分记录并分配给变量 [英] Splitting Up Records and assigning to variable

问题描述

尊敬的先生/女士，



iam有一行包含

563/90，Chitragupta Nagar | Alambagh |勒克瑙|北方邦|印度| 226005

输出应该是

Dear Sir/Madam,

iam having one row containing
563/90, Chitragupta Nagar | Alambagh | Lucknow | Uttar Pradesh | India | 226005
output should be

ADDRESS:Chitragupta Nagar
Alambagh
Lucknow
STATE:Uttar Pradesh
COUNTY:India
PINCODE:226005

如何在sql查询中执行此操作。请帮助我

how to do this in sql query. kindly help me

推荐答案

试试这样....

Hi,

Try like this....

DECLARE @String VARCHAR(1000), @CodePos INT

SELECT @String='563/90, Chitragupta Nagar | Alambagh | Lucknow | Uttar Pradesh | India | 226005', @CodePos=1
PRINT 'NO:563/90, ADDRESS:Chitragupta Nagar | STREET:Alambagh | CITY: Lucknow |STATE: Uttar Pradesh | COUNTRY : India | PINCODE: 226005'
SELECT @String='NO:'+REPLACE(@String,',',', ADDRESS:')
SELECT @CodePos=CHARINDEX( '|',@String,1)
SELECT @String=STUFF(@String,@CodePos+2,0,'STREET: ') --Street
SELECT @CodePos=CHARINDEX( '|',@String,@CodePos+2)
SELECT @String=STUFF(@String,CHARINDEX( '|',@String,@CodePos)+2,0,'CITY: ') -- City
SELECT @CodePos=CHARINDEX( '|',@String,@CodePos+2)
SELECT @String=STUFF(@String,CHARINDEX( '|',@String,@CodePos)+2,0,'STATE: ') -- State
SELECT @CodePos=CHARINDEX( '|',@String,@CodePos+2)
SELECT @String=STUFF(@String,CHARINDEX( '|',@String,@CodePos)+2,0,'COUNTRY: ') -- Country
SELECT @CodePos=CHARINDEX( '|',@String,@CodePos+2)
SELECT @String=STUFF(@String,CHARINDEX( '|',@String,@CodePos)+2,0,'PINCODE: ') -- Pincode
PRINT @String

-- Note : I am not sure about the Query because If any changes in your Source Data then we need to change the Query.

Regarrds，

GVPrabu

Regarrds,
GVPrabu

创建此功能...

Create this function...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(max))
AS
BEGIN
    DECLARE @Value varchar(max)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(
          @String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,
          @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(
          @String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

现在查询...

now query...

DECLARE @String VARCHAR(1000), @CodePos INT
 
SELECT @String='NO:563/90, ADDRESS:Chitragupta Nagar | STREET:Alambagh | CITY: Lucknow |STATE: Uttar Pradesh | COUNTRY : India | PINCODE: 226005'

select val from dbo.parsevalues(@String ,'|');

快乐编码！

：）

Happy Coding!
:)

抱歉，输出应该是小的变化 - -NO：563/90，地址：Chitragupta Nagar |街：Alambagh |城市：勒克瑙|状态：北方邦|国家：印度|密码：226005

sorry, small change in output it should be --NO:563/90, ADDRESS:Chitragupta Nagar | STREET:Alambagh | CITY: Lucknow |STATE: Uttar Pradesh | COUNTRY : India | PINCODE: 226005

这篇关于拆分记录并分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！