Split Fields and display query result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split Fields and display query result

Author

Topic

uxpme
Starting Member

5 Posts

Posted - 2007-03-21 : 01:29:13

Hi,
I'm having a problem in spliting the fields
I need to ru the following query to join two tables and getting the output as shown.

Query:
select cusl.user_name,
pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Output:
user_name bills_ref_info payee_acid cust_acid txn_amt
SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318
SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327
SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031
SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918

Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query.
Can this be done? Please guide me on this...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-21 : 01:34:00

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 01:35:31

[code]select
cusl.user_name,
parsename(replace(pmts.bill_ref_info, '#', '.'),3) as Field1,
parsename(replace(pmts.bill_ref_info, '#', '.'),2) as Field2,
parsename(replace(pmts.bill_ref_info, '#', '.'),1) as Field3,
pmts.payee_acid,
pmts.cust_acid,
pmts.txn_amt,
pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 01:40:15

And this, if your record contains '.' as a part of data:

select 
	cusl.user_name,
	replace(parsename(replace(replace(pmts.bill_ref_info,'.', char(255)), '#', '.'),3), char(255), '.') as Field1,
	replace(parsename(replace(replace(pmts.bill_ref_info,'.', char(255)), '#', '.'),2), char(255), '.') as Field2,
	replace(parsename(replace(replace(pmts.bill_ref_info,'.', char(255)), '#', '.'),1), char(255), '.') as Field3,
	pmts.payee_acid, 
	pmts.cust_acid, 
	pmts.txn_amt,
	pmts.pmt_id
from cusl, pmts
where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 01:53:41

I think original PARSENAME funcion only accepts four parts, from right to left.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 01:59:54

But the as per sample data posted by OP, PARSENAME should be sufficient, isn't it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 02:03:01