How many characters has my field?

Does anyone know a function in SQL or how I can get the amount of characters of a field?

I have a column named NU_IPS wich contains data varchar type, that has a % symbol at the end, like 9.7% and so on... But in original table it can't be like this (it has to bem float type), I just want the number content, like this 9.7 For that I need in DTS put a query that convert it. That's why I need a function or something that can get the quantity of characters of each field.

So, It would be someting like this...

select substring(convert(varchar(getSizeField() - 1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

It would cut always the last caracter, wich is '%'...

Any clues?
All posts are welcome, thanks.Look up the Len() - function ...|||Thanks Nephilim!

But why can't I put this function in the convert if it returns a int type?
I guess it can't process each field at time, 'cause we can admit there's a table in convert varchar paremeter, it's obvious.

Like:
select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

I've tried putting an IF statment...

Like:
if ((select len(nu_ipi) - 1 from dbo.t_STAGEAREACHAIR) = 1)
begin
select substring(convert(varchar(1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR
end

But it returns more than 1 value though.

Have you an idea how I can do it?
Thanks|||First of all, there really is no need to put a subselect in the select you posted :

select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

I think you are looking for something like this

select substring(convert(Varchar(10), NU_IPS), 1, len(convert(Varchar(10), NU_IPS)) -1 from dbo.t_STAGEAREACHAIR

however I'm not exactly certain I've understood your problem fully...|||maybe i don't understand it fully either, but NU-IPS is already a varchar, so...
select left(NU_IPS,len(NU_IPS)-1) ...or alternatively
select replace(NU_IPS,'%','') ...|||r937 and Nephilim
I think You got it, that's what I wanted.
It works...
Really thanks!
Juliane

how if esle statement works?

hi, everybody!

I have some problems with understanding howif esle statment in sql works. I'm trying to write my owncount()-function and its not working withif else :(( I've already implemented it withcase:
select sum(case (hiredate) when (null) then 0 else 1 end) as 'Count' from emp

It's working perfect. Now I want to have the same but usingif else:
select sum(
if( hiredate is null)
begin 0 end
else 1)
as 'Count' from emp

the error message is:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '0'.

What is wrong here?

Artur:

If / else does not work within the context of a select list like this; you need to implement as your first select statement with case statement or use a where condition such as something like:

select count(*)
from emp
where hiredate is not null

|||I think you are right, its not possible to use if/esle within select statement.
Thanks!!!
|||In SQL Server, IF - Else is a control flow statement that allows you to conditionaly run blocks of T-SQL code. Case is an expression that can be used inside some statements. SQL Server does not have a CASE Statement like some other languages.|||Artur,

You can have an 'if/then/else' statement within your code, it's just that you have to put 'case when' instead of 'if'. It works exactly the same way.

Try:

select sum(case when hiredate is null then 0 else 1 end) as 'Count'
from emp

Rob

How I Can ADD An Assemby to My DataBase File

I've created a DLL file that has some shared function taht I want to add to my MDF database for using it in the select statements

how I can add the functions in this DLL to my MDF file and use it

You need to use the CREATE ASSEMBLY statement. Start with the overview topic in BOL (here) which should point you at other topics.

Mike

|||

Thank you