comma delimited value compare/replace

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 2008 Forums

Transact-SQL (2008)

comma delimited value compare/replace

Author

Topic

telynch25
Starting Member

9 Posts

Posted - 2010-07-20 : 18:04:24

Hello all...

I am trying to write a function that takes a comma delimited value, and compares each value to replace a portion with a "/"... I started writing it, and I can see how to hard code it, but the values will never be the same, so I don't want to hard code every possible value (there's over 100 unique).

Ex. as400b, as400c, as400d, ...
would return as400b/c/d

Similiarly, it can be as400b, as400c, ciont031, ciont032, ciant031
would return as400b/c, ciont031/032, ciant031

Here's what I've got just playing around with it. The list isn't going to be made this way...I'm just trying to get a function that I can pass the pre-populated "list" to.

declare @List varchar(max)
SET @List = ''

select 
@list = @list + ServerName + ', '
from Server 
where StatusId = 1 and ServerName like 'as400%'
order by servername

set @List = SUBSTRING(@List, 1, LEN(@list)-1)
select @List

set @List = REPLACE(@list, ', as400', '/')
select @List

Thanks for the assistance!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-20 : 18:18:12

may i ask first why are you comma delimiting it? why not fix the problem at the source , that is when you are inserting into the table in the first place?

If you don't have the passion to help people, you have no passion

telynch25
Starting Member

9 Posts

Posted - 2010-07-20 : 19:37:28

It's actually being stored in the database as full values "as400a", "as400b", etc...each one is it's own seperate entry...The problem is, I need to put these values into a field in an application that is limited to 80 characters. I can't change that limitation...it's put on me by HP. I'm dynamically pulling out all of the names from a change request and trying to fit as much info about the servers in a brief description field as I can. Otherwise, all of the servers are being put in their respective areas in the correct manner. hope that helps explain the delima a little better.

telynch25
Starting Member

9 Posts

Posted - 2010-07-20 : 19:42:36

And don't get me wrong...I'm not asking for it to be written...if you can point me in a direction other then using creating a comma delimited value and then processing against that value...then I'd much appreciate it.

I'd like to figure out what exactly it is i'm doing along the way...Only way I'll learn more then what I already know...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 10:08:34

are you saying each value right now is being stored in seperate rows and/or fields? it is always helpful to state that in the beginning of your post. I am sure others probably assume it was comma delimited in the database. can you show a snapshot of the table schema and the field where these values are in..

If you don't have the passion to help people, you have no passion

telynch25
Starting Member

9 Posts

Posted - 2010-07-21 : 10:29:33

This is the basic structure of the table...some columns that are unnecessary are removed...

CREATE TABLE [dbo].[Server](
	[ServerName] [varchar](25) NOT NULL,
	[StatusId] [bigint] NOT NULL,
	[SpecialInstructions] [varchar](max) NULL,
 CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED 
(
	[ServerName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 10:49:28

will servernames ever start with numeric or always alpha?

If you don't have the passion to help people, you have no passion

telynch25
Starting Member

9 Posts

Posted - 2010-07-21 : 11:03:20

always alpha. Thanks for your help! It's greatly appreciated!!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-21 : 11:27:28

no problem Tom
try this, it might need some fudging and of course point it to your database and tables. and always please provide sample data as you see below with create temp table populate with data etc. makes it quicker to help folks out.



create table #meow(server_name varchar(400))
insert into #meow
values('as400b')
GO
insert into #meow
values('as400c')
GO

insert into #meow
values('ciont031')
GO

insert into #meow
values('ciont032')
GO

insert into #meow
values('ciant031')
GO

;with cte(Uniquem, ServerPrefix,ServerNum,ServerSuffix )  
as (SELECT
Left(server_name, PatIndex('%[0-9]%', server_name + '1') - 1) +
       REPLACE(
               REPLACE(server_name, Left(server_name, PatIndex('%[0-9]%', server_name + '1') - 1),'')    
               ,right(server_name, PatIndex('%[0-9]%', REVERSE(server_name) + '1') - 1)  ,'') Uniquem,
       Left(server_name, PatIndex('%[0-9]%', server_name + '1') - 1) as ServerPrefix,
       REPLACE(
               REPLACE(server_name, Left(server_name, PatIndex('%[0-9]%', server_name + '1') - 1),'')    
               ,right(server_name, PatIndex('%[0-9]%', REVERSE(server_name) + '1') - 1)  ,'') ServerNum,
       right(server_name, PatIndex('%[0-9]%', REVERSE(server_name) + '1') - 1) ServerSuffix 
  FROM #meow

)

SELECT DISTINCT s1.Uniquem +
		STUFF((SELECT DISTINCT TOP 100 PERCENT '/'+ s2.ServerSuffix
		         FROM cte AS s2 
		        WHERE s2.Uniquem = s1.Uniquem 
		        ORDER BY '/' + s2.ServerSuffix 
		          FOR XML PATH('')), 1, 1, '') AS conca
        
  FROM cte s1

 

 drop table #meow

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources