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 1132 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 - A Proposal
As we all know, one of the best things about python and other
scripting languages is dynamic typing (yes I know it has advantages
and disadvantages but I will not discuss them now). Dynamic typing
allows us to change types of...
|
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
completely but was unable to go through one question.
|
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 or is it a benefit that C knows nearly
nothing (what I can think about is that C is the largest common divisor
defined on most available platforms)?
|
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 is conversion of coordinates
from one frame of reference to another: spherical coordinates in the
ecliptic frame of reference, spherical coordinates in the equatorial frame
of reference, 3D rectangular coordinates in either, galactic coordinates,...
|
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 Safari and Opera. I am
doing a lot of dragdrop experimentation and in some situations need a
position reporting function. The function doesn't need to report the
positions of exotic elements like images in button elements; however, I
would like a...
| |
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 game with ASCII graphics and input via keyboard.
I'm trying to look for other games suitable to those restrictions.
Does anyone knows a good candidate?
|
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
//---------------------------------------------------------------------------------
struct DataType
{
int key;
string name;
};
class Heap
|
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
without compression). It has it's words and dwords stored in little-
endian, so I do a conversion to big-endian when reading full words or
dwords.
I have done this because my system is big-endian.
But now... what if one compiles the library in a...
|
by: guthena |
last post by:
Write a small C program to determine whether a machine's type is
little-endian or big-endian.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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 can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |