quote:

---

You'll also have to define some rules. Like what should be returned for these values:
'he is my #2 man in charge'
'#please ignore these comments#'
'###'
'#N34#24#'
'#ignore this# but use this: #20# ######'

---

quote:

---

What is the max(datalength) of this column?

---

set nocount on
create table #junk (rowid int identity(1,1) primary key clustered,  explain text)
go
insert #junk (explain)
select '#24# Medicare Crossover Claim Pays at Zero Dollars #N36# Invalid Lifetime Reserve Days #N34# Invalid Bill Type' union all
select 'he is my #2 man in charge' union all
select '#please ignore these comments#' union all
select '###' union all
select '#N34#24#' union all
select '#ignore this# but use this: #20# ######'

go

--create a function to return the codes as a table
create function dbo.fn_ExplainCodes(@explain varchar(2000))
returns @codes table (code varchar(50))
as
begin
	/*
	this function extracts values BETWEEN hash <#> characters
	assumes:
		input is <= 2000 characters
		values between hash chars is <= 50
	*/
	declare	@idx		int
		,@inCode	bit
		,@len		int
		,@c		char(1)
		,@val		varchar(50)
	
	select	@idx 		= 0
		,@inCode	= 0
		,@val		= ''
	
	set @len = len(@explain)
	
	while @idx <= @len
	begin
		select	@idx = @idx + 1	
			,@c = substring(@explain, @idx, 1)
	
		if @c = '#' and @inCode = 0
		begin
			select	@val = ''
				,@incode = 1
		end
		else if @c = '#' and @inCode = 1
		begin
			select @inCode = 0
			insert @codes
			select @val where @val <> ''
		end
		else if @inCode = 1
		begin
			set @val = @val + @c
		end
	end
	return
end
go


--use the function to populate a table of codes
declare @codes table (rowid int identity(7,-1), ExplainCode varchar(50))
declare @explain varchar(2000)
	,@i int
select @i = max(rowid) from #junk
while @i > 0
begin
	select @explain = substring(explain,1,2000) from #junk where rowid = @i
	insert @codes
	select code from dbo.fn_ExplainCodes(@explain)
	select @i = @i - 1
end

select * from @codes order by 1


go
--clean up
drop function dbo.fn_ExplainCodes
drop table #junk

set nocount on
create table #junk (rowid int identity(1,1) primary key clustered,  explain text)
go
insert #junk (explain)
select '#24# Medicare Crossover Claim Pays at Zero Dollars #N36# Invalid Lifetime Reserve Days #N34# Invalid Bill Type' union all
select 'he is my #2 man in charge' union all
select '#please ignore these comments#' union all
select '###' union all
select '#N34#24#' union all
select '#ignore this# but use this: #20# ######'

go

--create a function to return the codes as a table
create function dbo.fn_ExplainCodes(@explain varchar(2000))
returns varchar(2000)
as
begin
	/*
	this function extracts values BETWEEN hash <#> characters
	assumes:
		input is <= 2000 characters
		values between hash chars is <= 50
	*/
	declare @codes table (rowid int identity(1,1), code varchar(50))
	declare @codesCSV varchar(2000)

	declare	@idx		int
		,@inCode	bit
		,@len		int
		,@c		char(1)
		,@val		varchar(50)
	
	select	@idx 		= 0
		,@inCode	= 0
		,@val		= ''
	
	set @len = len(@explain)
	
	while @idx <= @len
	begin
		select	@idx = @idx + 1	
			,@c = substring(@explain, @idx, 1)
	
		if @c = '#' and @inCode = 0
		begin
			select	@val = ''
				,@incode = 1
		end
		else if @c = '#' and @inCode = 1
		begin
			select @inCode = 0
			insert @codes
			select @val where @val <> ''
		end
		else if @inCode = 1
		begin
			set @val = @val + @c
		end
	end

	select @codesCSV = coalesce(@codesCSV + ',' + code, code) from @codes
	return @codesCSV
end
go


--use the function to return Code comma seperated values (CSV)
select rowid, dbo.fn_ExplainCodes(explain) CodeCSV from #junk


go
--clean up
drop function dbo.fn_ExplainCodes
drop table #junk

quote:

---

Originally posted by namasteall2000

No we can define the length of the this column.

But yes rule is defined that everytime ruleid is going to be in ## e.g #12# or #N34#.

Do you think we can have some solution for that?

Thanks

---

quote:

---

You could store your data in a normalized manner and then you wouldn't have these issues. You should never stuff multiple entries into a single row/column in a table -- it should be broken out into related tables.

see: http://www.datamodel.org/NormalizationRules.html

---

quote:

---

Originally posted by namasteall2000

Hi – I have a column named as ‘Explain’ and data type as ‘’Text’
Explain
--
#24# Medicare Crossover Claim Pays at Zero Dollars #N36# Invalid Lifetime Reserve Days #N34# Invalid Bill Type

Now my problem is – as this is a text column – there is very few function which works. I want where ever we get # sign we need to pick up code from ##.
Like output should be

Explain
--
24
N36
N34

Could anyone suggest some solution?

---

			insert @codes
			select @val where @val <> ''

			if not exists(select 'tg' from @codes where code = @val)
				insert @codes (code)
				select @val where @val <> ''