integer to string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

integer to string

Author

Topic

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-06-19 : 16:33:38

can I select an integer from SQL and convert it to a string in the SELECT statement? If anyone knows a syntax for this it would be greatly appreciated. Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-19 : 16:37:39

select convert(varchar,<urcolumn>) from <table>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 01:49:46

also make sure you specify an explicit length for varchar

select convert(varchar(length),<urcolumn>) from <table>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 01:53:25

also see
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 18:57:01

Now that you have the conversion, please tell us, what are you going to do with the result?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

KMontz
Starting Member

1 Post

Posted - 2013-04-22 : 15:22:08

In case anyone is looking for the way to get numbers from a mixed string:
select dbo.UDF_ParseNumbers('ABCD1234') will yield 1234. Here's a UDF that works:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[UDF_ParseNumbers]
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END

Keith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources