拆分记录并分配给变量 | 拆分记录并分配给变量

本文介绍了拆分记录并分配给变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

尊敬的先生/女士，

iam有一行包含

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

输出应该是

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

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

how to do this in sql query. kindly help me

推荐答案

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

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!
:)

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

1403页，肝出来的..