quote:

---

Originally posted by visakh16

use ASCII() function. you might have to loop over entire length of number to convert all characters to ASCII.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Studevs

Dear All,

I have a table with two columns:

name and number...the type of both columns is varchar.
now how can ik select the both columns buth the value of column number most be returned in ascii.
Please some help.

Thanks in advance

---

quote:

---

Originally posted by visakh16

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

CREATE TABLE [dbo].[customer](
	[name] [varchar](50) NULL,
	[number] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [qvtelemetrie].[dbo].[customer]
           ([name]
           ,[number])
     VALUES
           ('name','3214')
GO

quote:

---

Originally posted by madhivanan

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Studevs

Hi madhivanan,

This is the code:

CREATE TABLE [dbo].[customer](
	[name] [varchar](50) NULL,
	[number] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [qvtelemetrie].[dbo].[customer]
           ([name]
           ,[number])
     VALUES
           ('name','3214')
GO

quote:

---

Originally posted by madhivanan

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

---

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by Studevs

Hi madhivanan,

This is the code:

CREATE TABLE [dbo].[customer](
	[name] [varchar](50) NULL,
	[number] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [qvtelemetrie].[dbo].[customer]
           ([name]
           ,[number])
     VALUES
           ('name','3214')
GO

quote:

---

Originally posted by madhivanan

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

---

---

What is your expected result?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by vaibhavktiwari83

ASCII code for the number 1234 will give you the ascii code of 1 only.
what you need



Vabhav T

---

quote:

---

Originally posted by vaibhavktiwari83

ascii is for only one character.
what you want you want to concatenate all the ascii code for each character or what.

Vabhav T

---

quote:

---

Originally posted by Studevs

Hi Vabhav,

Thanks,
yes that's exatly what i want :)

quote:

---

Originally posted by vaibhavktiwari83

ascii is for only one character.
what you want you want to concatenate all the ascii code for each character or what.

Vabhav T

---

---

quote:

---

Originally posted by Studevs

it's a requirement

---

CREATE FUNCTION dbo.GetAsciiValue
(
@str varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @res varchar(1000)

SELECT @res=COALESCE(@res,'')+CAST(ASCII(SUBSTRING(@str,number,1)) AS varchar(5))
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND LEN(@str)

RETURN @res
END

then use it like

select name,number,dbo.GetAsciiValue(number) AS AsciiEqv
from yourtable

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Studevs

it's a requirement

---

create a function like this

CREATE FUNCTION dbo.GetAsciiValue
(
@str varchar(100)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @res varchar(1000)

SELECT @res=COALESCE(@res,'')+CAST(ASCII(SUBSTRING(@str,number,1)) AS varchar(5))
FROM master..spt_values
WHERE type='p'
AND number BETWEEN 1 AND LEN(@str)

RETURN @res
END

then use it like

select name,number,dbo.GetAsciiValue(number) AS AsciiEqv
from yourtable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---