Splitting Up Records and assigning to variable

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

CSS

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

how to do this in sql query. kindly help me

Posted 13-Jun-13 20:15pm

DhanaNat

Add a Solution

Comments

RelicV 14-Jun-13 2:49am

Does this requirement needed only for one record?

gvprabu 14-Jun-13 6:12am

See if some records didn't have Street or some other values then values will be like "563/90, Chitragupta Nagar | | Lucknow | Uttar Pradesh | India | 226005" Right...U need to add the Details like Address, Street , City ect... right

gvprabu 14-Jun-13 6:41am

I gave the solution... But I am not sure, It will be the best for all cases... See if Street is not there then "| Street: | " like this data will Update. So check with your actual Data

3 solutions

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)

gvprabu · Answer 1 · 2013-06-14T00:34:00

Hi,

Try like this....

SQL

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

Aarti Meswania · Answer 2 · 2013-06-14T01:01:00

Create this function...

SQL

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...

SQL

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

DhanaNat · Answer 3 · 2013-06-13T20:35:00

Solution 1

Posted 13-Jun-13 20:35pm

DhanaNat

Splitting Up Records and assigning to variable

3 solutions

Solution 3

Solution 4

Solution 1

Add your solution here

Preview 0