拆分记录并分配给变量

拆分记录并分配给变量

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

问题描述

限时删除!!

尊敬的先生/女士,



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

推荐答案

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页,肝出来的..

09-08 10:38