subtrat one string from concatenation

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

SQL Server 2005 Forums

Transact-SQL (2005)

subtrat one string from concatenation

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-06 : 10:49:30

anybody have a solution of it:

i am concatenating the variable , it wroking fine but concatenating one extra value (initially value) . i dont want to get the first value till to comma ','.

i am trying this:

/*=========================================================================================================================

Name : Mehmood Ahmed
Date : 06-April-2010
Project: MBFSS
Module: ARM
Purpose : FOr fetching And modifying the foreign key constraints

============================================================================================================================*/
DECLARE @CONS_NME VARCHAR(100)
DECLARE @FK_TBL_NME VARCHAR(100)
DECLARE @FK_COL_NME VARCHAR(100)
DECLARE @PK_TBL_NME VARCHAR(100)
DECLARE @PK_COL_NME VARCHAR(100)
DECLARE @count_repeated_cons int
DECLARE @repeated_cons_nme varchar(200)
DECLARE @concat_col_names varchar(200)
Declare @fkey int
DECLARE @pos int
DECLARE @ind bit

DECLARE get_data_cur CURSOR FOR
select Object_Name(f.constid) ,
Object_Name(f.fkeyid) ,f.fkey,
c1.name ,
Object_Name(f.rkeyid) ,
c2.name
from dbo.sysforeignkeys f
inner join dbo.syscolumns c1 On c1.id = f.fkeyid And c1.colid = f.fkey
inner join dbo.syscolumns c2 On c2.id = f.rkeyid And c2.colid = f.rkey
where Object_Name(f.fkeyId) = 'bp_bank' /* and Object_Name(f.constid) = 'R_1425' */
order by 1,2,3,4
set @ind = 0
open get_data_cur
fetch get_data_cur into @CONS_NME, @FK_TBL_NME,@fkey,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME
--set @pos = @COUNT_REPEATED_CONS
--SET @concat_col_names = @FK_COL_NME
while (@@fetch_status = 0 )
BEGIN

SELECT @repeated_cons_nme = Object_Name(f.constid), @count_repeated_cons = Count(1) /*AS Counts*/
FROM dbo.sysforeignkeys f
INNER JOIN dbo.syscolumns c1
On c1.id = f.fkeyid And c1.colid = f.fkey and object_name(f.fkeyId) = 'bp_bank' and Object_Name(f.constid) = @CONS_NME
INNER JOIN dbo.syscolumns c2
On c2.id = f.rkeyid And c2.colid = f.rkey
GROUP BY f.constid
HAVING COUNT(1) >= 1

select @repeated_cons_nme, @count_repeated_cons ,@CONS_NME, @FK_TBL_NME,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME

IF @COUNT_REPEATED_CONS = 1 and @FK_COL_NME = @PK_COL_NME
BEGIN
SET @concat_col_names = @FK_COL_NME
END
ELSE IF @COUNT_REPEATED_CONS > 1 and @FK_COL_NME = @PK_COL_NME
BEGIN

SET @concat_col_names = @concat_col_names + ' , ' + @FK_COL_NME
select @concat_col_names concat_total, @FK_COL_NME fk_col, @COUNT_REPEATED_CONS cout_repeated, @pos cout_previous
END

fetch get_data_cur into @CONS_NME, @FK_TBL_NME,@fkey,@FK_COL_NME , @PK_TBL_NME, @PK_COL_NME

IF @FK_COL_NME = @PK_COL_NME and @CONS_NME = @repeated_cons_nme and @COUNT_REPEATED_CONS = 1
BEGIN
select 'single fk :'+ @CONS_NME + ' : ' + @PK_TBL_NME

END
ELSE IF @FK_COL_NME = @PK_COL_NME and @CONS_NME = @repeated_cons_nme and @COUNT_REPEATED_CONS >= 1
BEGIN
select 'composite fk :'+ @CONS_NME + ' : ' + @PK_TBL_NME

END

END

close get_data_cur
deallocate get_data_cur

SELECT @concat_col_names

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 11:30:39

I'm not exactly sure what you mean, but try this.

SELECT STUFF(@concat_col_names,1,1,'')

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-06 : 11:38:41

quote:

I am concatenating the variable , it wroking fine but concatenating one extra value (initially value) . i dont want to get the first value till to comma ','.

What i understand is that you want the value in list after first comma.
If my understanding is correct then you can try this:

Declare @TestVal varchar(500)
Set @TestVal = 'ab,123,aded123,78925'
Select substring(@TestVal,charindex(',',@TestVal)+1,len(@TestVal))

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-07 : 05:42:03

CURRENT OUTPUT IS :
Actual Output is :

R_1003 1 R_1003 BP_BANK account_type_cde ACCOUNT_TYPE_CODE account_type_cde

R_1004 3 R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_primary_id

account_type_cde , BP_primary_id BP_primary_id 3 NULL

composite fk :R_1004 : BP_RELATIONSHIP

R_1004 3 R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP BP_secondary_id

account_type_cde , BP_primary_id , BP_secondary_id BP_secondary_id 3 NULL

composite fk :R_1004 : BP_RELATIONSHIP

R_1004 3 R_1004 BP_BANK relationship_cde BP_RELATIONSHIP relationship_cde

account_type_cde , BP_primary_id , BP_secondary_id , relationship_cde relationship_cde 3 NULL

R_1425 1 R_1425 BP_BANK business_partner_id BP_MAIN business_partner_id

single fk :R_1425 : BP_MAIN

business_partner_id

Desired Output :

R_1003 1 R_1003 BP_BANK account_type_cde ACCOUNT_TYPE_CODE account_type_cde

account_type_cde account_type_cde 1 NULL

R_1004 3 R_1004 BP_BANK BP_primary_id BP_RELATIONSHIP BP_primary_id

BP_primary_id BP_primary_id 3 NULL

composite fk :R_1004 : BP_RELATIONSHIP

R_1004 3 R_1004 BP_BANK BP_secondary_id BP_RELATIONSHIP BP_secondary_id

BP_primary_id , BP_secondary_id BP_secondary_id 3 NULL

composite fk :R_1004 : BP_RELATIONSHIP

R_1004 3 R_1004 BP_BANK relationship_cde BP_RELATIONSHIP relationship_cde

BP_primary_id , BP_secondary_id , relationship_cde relationship_cde 3 NULL

R_1425 1 R_1425 BP_BANK business_partner_id BP_MAIN business_partner_id

business_partner_id business_partner_id 1 NULL
single fk :R_1425 : BP_MAIN

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-07 : 05:53:09

i have used this substring(@TestVal,charindex(',',@TestVal)+1,len(@TestVal)) but same effect coming out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources