Hi NG,

my problem:

In a select statement I read a string from a varchar(255) column.
The results may be
1.) "0, 0"
2.) "100, 200, 1"
3.) ""

I want to change the strings, so that every string has a format like:
"number, number, number".

The strings should look like:
1.) "0, 0, 1" (add a ', 1')
2.) "100, 200, 1" (remains unchanged)
3.) "0, 0, 1" (add a '0, 0, 1')

How can I do that? My first idea was to count the comma-character.
Add a "0, 0, 0" if no comma is found
Add a ", 1" if one comma is found
Add nothing if 2 comma were found.

Is there a sql-function that counts characters in a string? I didn't find
anything suitable in my ase documentation (ASE 11.9.2, linux).

Any help is welcome! Thanks in advance,

Roland

Roland Reichenberg wrote:

datalength() is the function you're looking for. But I'd
seriously question the design if you're using strings to
store aggregates of discrete numeric elements.


Larry Coon
University of California

Hi Larry,

thanks for your answer!

unfortunately data_length() does not help. I neet to count the numbers of
_one_ special character in a string. Sorry, if I did not describe my problem
clearly enough. I need a function which returns 2 if it counts the numbers
of the character "," in the string like "100, 100, 100"

However, I have found a workaround. I'll use the LIKE-keyword in my where
clause (WHERE string LIKE "%,%,%". So I can be sure that the query returns
datasets with 2 or more "," in the string.

Thanks again!

Roland

I hope if you play with charindex and substring then you can get the result.
Try it.
Ram DSL


"Roland Reichenberg" <r.reichenberg (AT) gmx (DOT) de> wrote

hi
can u help me out
my column contains data like firstname,lastname.
i want the output to display like lastname,firstname.
can u guide me for same



Roland Reichenberg wrote:
Unregistered
-----------------------------------------------------------------------
Posted via http://www.webservertalk.co
-----------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message104238.htm

I cant understand fully...
But this is my understanding....

table1...
------------------
Name
------------------
ram,mohan
ram,kumar
------------------

the output u r expecting:

mohan,ram
kumar,ram


query:


select substring(charindex(Name,',')+1,data_length(Name)) +',' +
substring(1,charindex(Name,',') -1) from table1

Ram DSL
www.dslsoft.net

reachram (AT) bharatmail (DOT) com (Ram DSL) wrote in message news:<f1565749.0402090705.702d755a (AT) posting (DOT) google.com>...

My fullname column in master..syslogins contains the data in Firstname,
Middle Initial, Lastname format. The procedure below will list the logins in
Lastname, Firstname, Middle Initial format. It is not 100% due to the
occasional 2 word last name and the precence of a suffix such as Jr. or III.

create procedure sp__helpfullname @limit varchar(30) = null

as

set nocount on

declare X cursor for select convert(varchar(10), name)

, fullname

, convert (varchar(15), dbname)

, convert (varchar(8), pwdate, 1)

, convert (varchar(8), accdate, 1)

, totio

, totcpu

from master..syslogins

declare @LOGIN varchar(10)

, @FNAME varchar(32)

, @REVERSE varchar(32)

, @DEFDB varchar(15)

, @PWDCHG varchar(8)

, @ACCDAT varchar(8)

, @TOTIO int

, @TOTCPU int

create table #mytemp (

LOGIN varchar(10)

, FNAME varchar(32)

, DEFDB varchar(15)

, PWDCHG varchar(8)

, ACCDAT varchar(8)

, TOTIO int

, TOTCPU int)

open X

fetch X into @LOGIN, @FNAME, @DEFDB, @PWDCHG, @ACCDAT, @TOTIO, @TOTCPU

while @@sqlstatus = 0

begin

declare @x int

select @x = isnull(datalength(@FNAME), 0)

select @REVERSE = isnull(@FNAME, '** undefined **')

while @x > 0

begin

if substring(@FNAME, @x, 1) = ' '

begin

select @REVERSE = substring(@FNAME, @x + 1, datalength(@FNAME)) + ', ' +
substring(@FNAME, 1, @x -1)

break

end

else

begin

select @x = @x - 1

end

end

insert #mytemp values (@LOGIN, @REVERSE, @DEFDB, @PWDCHG, @ACCDAT, @TOTIO,
@TOTCPU)

fetch X into @LOGIN, @FNAME, @DEFDB, @PWDCHG, @ACCDAT, @TOTIO, @TOTCPU

end

if @limit is null

begin

select FNAME, LOGIN, DEFDB, PWDCHG, ACCDAT, TOTIO, TOTCPU from #mytemp order
by FNAME

end

else

begin

select FNAME, LOGIN, DEFDB, PWDCHG, ACCDAT, TOTIO, TOTCPU

from #mytemp

where upper(FNAME) like upper(@limit) + '%'

order by FNAME

end

go

if exists (select * from sysobjects where name ='sp__helpfullname')

begin

declare @msg varchar(200)

select @msg="stored procedure sp__helpfullname was created in the
"+db_name()+" db."

print @msg

end

go

grant execute on sp__helpfullname to public

go

"Ram DSL" <reachram (AT) bharatmail (DOT) com> wrote

Hi Roland,
I have got a simple function. But it will work in 12 and above... You
may try this.
Ram DSL
London
www.dslsoft.net

/*---------------- From Here -----------------------------*/

/*
table1
------
pr-id col
----- ---
1 "0,0"
2 "100,200,1"
3 ""
----------------------
*/
Declare @tempid integer
Declare @tempcol integer
Declare @templen integer

select pr-id,col into #temp from table1

set rowcount=1

while 1=1
{
select @tempid = pr-id,
@tempcol = col ,
@tempnos = datalength(convert(varchar(255),col)) -
datalength(convert(varchar(255), str_replace(col, ",", null))
from #temp
if (@@rowcount=0 )
break;
if (@tempnos = 1)
{
update table1
set col = col + ',1')
where col = @tempcol
and pr-id = @tempid
}
if (@tempnos = 0)
{
update table1
set col = col + '0,0,1')
where col = @tempcol
and pr-id = @tempid
}
delete from #temp
where col = @tempcol
and pr-id = @tempid
}
set rowcount
select * from table1

/*---------------- Upto Here -----------------------------*/

Hi Roland,
This will work for any version ....
Hv a nice week end.
Ram DSL,London
www.dslsoft.net

/***************** From Here ******************************/
/*
create table table1 (prid int, col varchar(50))

insert into table1 values (1,"0,0")
insert into table1 values (2,"100,200,1")
insert into table1 values (3,"")
*/

Declare @tempid integer
Declare @tempcol varchar(50)
Declare @templen integer
Declare @tempnos integer

Declare @count int
Declare @item int

select "before Update",* from table1
select prid,col into #temp from table1

set rowcount 1

while 1=1
begin
select @tempid = prid,
@tempcol = col
from #temp
if (@@rowcount=0 )
break

select @item =1
select @count =0

while @item <= datalength(@tempcol)
begin
if substring(@tempcol,@item,1)=","
begin
select @count = @count+1
end
select @item = @item+1
end
select @tempnos = @count
if (@tempnos = 1)
begin
update table1
set col = col + ',1'
where col = @tempcol
and prid = @tempid
end
if (@tempnos = 0)
begin
update table1
set col = col + '0,0,1'
where col = @tempcol
and prid = @tempid
end
delete from #temp
where col = @tempcol
and prid = @tempid
end
set rowcount 0
drop table #temp
select "After Update",* from table1

/***************** Upto Here ******************************/


reachram (AT) bharatmail (DOT) com (Ram DSL) wrote in message news:<f1565749.0402120311.3f54cef9 (AT) posting (DOT) google.com>...