Please Help for Function

Hi
I am looking for freequently using fuction like Find number of characters in a word.

Ex:
[code]
Declare @.Str='AXG00023'
If I use the function like getnumberofChars(@.Str) Then it has to give the result is 3.
Because rest of all numeric valus.

Is we have any function like this in SqlServer

please Help me

:confused:Yes,

You can use the DATALENGTH(), which will return the number of characters in a string.

If you are using that on a char datatype, you may want to use a Rtrim() on the field first before getting that datalenght.

ie. datalength(rtrim(@.stringname))

Scooter Mcfly|||DECLARE
@.Str varchar(50),
@.i int,
@.x int

SET @.Str ='AXG00X023'
SET @.i = 0
SET @.x=1

WHILE @.x <= DATALENGTH(@.Str) BEGIN
IF ISNUMERIC(SUBSTRING(@.Str,@.x,1)) = 0
SET @.i=@.i+1

SET @.x=@.x+1
END

print @.i

If you are using SQL Server 2000 you could put this code into your own user defined function.

Please HELP

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

Quote:

Originally Posted by gggram2000

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)|||

Quote:

Originally Posted by amitpatel66

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)

Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.

A question would be how to insert a value (eg. Name) into a table row block. Like: Insert Into Table (ClolumnName) Value(@.ColumnName) But I want to enter it into and existing column/row to replace the value of that specific column with "Name". Let's say for that column it has "None"...I want to change it to "Name"...how do i do that?

Pl/SQL Beginner Problem, Selecting all records?

SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.just remove the condition tht where student id = 156:

SELECT *
INTO student_rec
FROM student

above script will select all the student in student and store it into the sudent_rec .

RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');

when you want to raise the exeception just write it ther is no record in the table instad of student with id= 156 is not in the database.|||Hi,

Use a cursor & remove the Hard coded value 156 in the query.

Originally posted by iknownothing
SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.|||When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Hi,

Use CURSOR to avoid this error. Because you can fetch only one row into the record type student_rec . When the value 156 is harcoded in the query , exactly one row is fetched into student_rec . So it works fine. But once you remove the hard coded value 156 from the query, all the records are fetched . Since the record type student_rec can accept only one value, it displays the error ORA-01422: exact fetch returns more than requested number of rows. To avoid this error & fetch all the records into student_rec, use a CURSOR.

Originally posted by iknownothing
When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Its ok, fixed it! Thanks.|||As an aside from your question, you should get out of the habit of doing this:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;

All that does is potentially hide errors from the user and allow inconsistent partial transactions to be committed. It should be simply:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
END;