Hi guys,
I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.
Please help.
Here's is the sample data of the table that I'm manipuilating
code name
code name
code_1 name_1
code_2 name_2
tues_2 name_4
code_4 name_3
>>> What am trying to get is the maximum index of the code
Below is my function
declare @.codeIndex varchar(20)
select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'
print @.codeIndex
if @.codeIndex is null
begin
print 'Code index is null
end
else
begin
print 'we got an index!'
end
>>> the output of this is always null even it's returning an index
Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||
Hi NNTP,
Thanks! I already figured it out; maybe, I'm just exhausted last friday. 
