I got little question. I want to generate auto customernumber, with Surname as startpoint. I take first two chars from surname and convert them to ascii.
But my function returns the varchar as 147 when it should be 7869.
so the function sums 78 + 69. So the question is, how do i paste 78 + 69 together in a varchar. -
USE [SoevereinCRM]
-
GO
-
/****** Object: UserDefinedFunction [dbo].[F_Create_KlantNummer] Script Date: 10/31/2007 16:12:04 ******/
-
SET ANSI_NULLS ON
-
GO
-
SET QUOTED_IDENTIFIER ON
-
GO
-
-- =============================================
-
-- Author: Wouter Neuteboom
-
-- Create date: 31-10-2007
-
-- Description: KlantNummer creëren
-
-- =============================================
-
ALTER FUNCTION [dbo].[F_Create_KlantNummer]
-
(
-
@Achternaam nvarchar(50)
-
)
-
RETURNS nvarchar(6)
-
AS
-
BEGIN
-
DECLARE @KlantNummer nvarchar(6)
-
DECLARE @string nvarchar(2)
-
DECLARE @position int
-
DECLARE @integer int
-
-
SET @string = UPPER(SUBSTRING(@Achternaam, 1, 2))
-
SET @position = 1
-
SET @KlantNummer = '000000'
-
-
WHILE @position <= 2
-
BEGIN
-
SET @integer = (SELECT ASCII(SUBSTRING(@string, @position, 1)))
-
SET @KlantNummer = @KlantNummer + @integer
-
SET @position = @position + 1
-
END
-
RETURN @KlantNummer
-
END
-
-
4 1128 ck9663 2,878
Recognized Expert Specialist
I got little question. I want to generate auto customernumber, with Surname as startpoint. I take first two chars from surname and convert them to ascii.
But my function returns the varchar as 147 when it should be 7869.
so the function sums 78 + 69. So the question is, how do i paste 78 + 69 together in a varchar. -
USE [SoevereinCRM]
-
GO
-
/****** Object: UserDefinedFunction [dbo].[F_Create_KlantNummer] Script Date: 10/31/2007 16:12:04 ******/
-
SET ANSI_NULLS ON
-
GO
-
SET QUOTED_IDENTIFIER ON
-
GO
-
-- =============================================
-
-- Author: Wouter Neuteboom
-
-- Create date: 31-10-2007
-
-- Description: KlantNummer creëren
-
-- =============================================
-
ALTER FUNCTION [dbo].[F_Create_KlantNummer]
-
(
-
@Achternaam nvarchar(50)
-
)
-
RETURNS nvarchar(6)
-
AS
-
BEGIN
-
DECLARE @KlantNummer nvarchar(6)
-
DECLARE @string nvarchar(2)
-
DECLARE @position int
-
DECLARE @integer int
-
-
SET @string = UPPER(SUBSTRING(@Achternaam, 1, 2))
-
SET @position = 1
-
SET @KlantNummer = '000000'
-
-
WHILE @position <= 2
-
BEGIN
-
SET @integer = (SELECT ASCII(SUBSTRING(@string, @position, 1)))
-
SET @KlantNummer = @KlantNummer + @integer
-
SET @position = @position + 1
-
END
-
RETURN @KlantNummer
-
END
-
-
try this technique: - select cast(ascii(substring('surname',1,1)) as varchar)
-
,cast(ascii(substring('surname',2,1)) as varchar),
-
-
cast(ascii(substring('surname',1,1)) as varchar)
-
+cast(ascii(substring('surname',2,1)) as varchar)
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
ck9663 2,878
Recognized Expert Specialist
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
the reason your function is returning the sum is because you're adding two integer numbers....try to convert both of them to varchar before adding, that'll concatenate those two variable, not add them
- SET @integer = (SELECT cast(ASCII(SUBSTRING(@string, @position, 1)) as nvarchar ))
Works great now, thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tongu? Yumruk |
last post by:
I have a little proposal about type checking in python. I'll be glad
if you read and comment on it. Sorry for my bad english (I'm not a
native English speaker)
A Little Stricter Typing in Python...
|
by: Usman |
last post by:
Huy everyone ,
Well I am not a big C++ programmer , I am just a little
young kid on it tryint to learn . Actually I was given an
assignment last week by my teacher which I solved
...
|
by: Martin Marcher |
last post by:
Hi,
I've read several questions and often the answer was
'C knows nothing about .'
So if C knows that little as some people say, what are the benefits, I
mean do other languages know more...
|
by: Tom Dacon |
last post by:
I'm curious to see if anyone has an opinion on this little design question -
I'm doing a computational astronomy library in C#, purely for my own use,
and one of the things that happens regularly...
|
by: petermichaux |
last post by:
Hi,
It seems like determining element position in a web page is a difficult
task. In the position reporting source code I've looked at there are
special fixes for at least some versions of...
| |
by: Gaijinco |
last post by:
Hi my name is Carlos Obregón and I'm currently a profesor of C/C++
programming at the CUMD in Bogotá Colombia.
This last term I ask my students to develop an implementation of the
minesweeper...
|
by: wishbone34 |
last post by:
Hi, I have a question regarding the use of a couple functions I have for an assignment.. first here is the header file that im trying to use...
|
by: Javier |
last post by:
Hello people,
I'm recoding a library that made a few months ago, and now that I'm
reading what I wrote I have some questions.
My program reads black and white images from a bitmap (BMP 24bpp...
|
by: guthena |
last post by:
Write a small C program to determine whether a machine's type is
little-endian or big-endian.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |