Little function question

[Wouter84]

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

[ck9663]

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)

[Wouter84]

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

[ck9663]

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

[Wouter84]

SET @integer = (SELECT cast(ASCII(SUBSTRING(@string, @position, 1)) as nvarchar ))

Works great now, thanks