Passing varchar length to CONVERT

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

Passing varchar length to CONVERT

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-06-22 : 19:03:29

Wondering if it's possible to pass the result of a function -- in this case, LEN() -- to the length argument of CONVERT?

1st result:


SELECT Name  
FROM sys.objects
WHERE Name = "systypedsubobjs"

Name
--------------------------------------------------------------------------------------------------------------------------------
systypedsubobjs

Field length shown way too long for reporting.

What's the length?

SELECT LEN(Name) AS Name 
FROM sys.objects
WHERE Name = "systypedsubobjs"

OK, now pass that value to VARCHAR in:

SELECT CONVERT(VARCHAR(15),Name)  
FROM sys.objects
WHERE Name = "systypedsubobjs"

Name
---------------
systypedsubobjs

I want to bypass these three steps, and have returned the exact string length, whatever that may be. Not operational, but here's the idea:

SELECT CONVERT(VARCHAR(/*LEN(NAME)*/),Name) AS Name
FROM sys.objects
WHERE Name = "systypedsubobjs"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-23 : 02:14:46

I think you'd have to use dynamic sql for this...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-23 : 02:30:15

Hello,

Unfortunately, you cannot use a variable to define a length of varchar data type.
However, varchar has a changable length depending on a value.

Best regards,

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-23 : 04:22:52

Also, all records must have same datatype and precision for any given column.

Why don't you just use RTRIM?

SELECT RTRIM(Name)
FROM sys.objects
WHERE Name = 'systypedsubobjs'

N 56°04'39.26"
E 12°55'05.63"

dmilam
Posting Yak Master

185 Posts

Posted - 2010-06-23 : 12:24:03

Thanks, all. I tried RTRIM, but it doesn't seem to trim the trailing field spaces here.

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 12:54:16

quote:
Originally posted by dmilam

Thanks, all. I tried RTRIM, but it doesn't seem to trim the trailing field spaces here.

Then you're doing something wrong

Post the code and sample data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 12:58:38

Run this


CREATE TABLE #myTable99(myName varchar(8000))
GO

INSERT INTO #myTable99(myName)
SELECT 'Brett1' UNION ALL
SELECT 'Brett2 ' UNION ALL
SELECT 'Brett3  ' UNION ALL
SELECT 'Brett4   '
GO


SELECT '"'+myName+'"', '"'+RTRIM(myName)+'"'
  FROM #myTable99
GO

DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-23 : 12:59:39

quote:
Originally posted by Devart

Hello,

Unfortunately, you cannot use a variable to define a length of varchar data type.

Ummmmm...that's noty quite true

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dmilam
Posting Yak Master

185 Posts

Posted - 2010-06-25 : 21:28:12

[quote]Originally posted by X002548

Run this


CREATE TABLE #myTable99(myName varchar(8000))
GO

INSERT INTO #myTable99(myName)
SELECT 'Brett1' UNION ALL
SELECT 'Brett2 ' UNION ALL
SELECT 'Brett3  ' UNION ALL
SELECT 'Brett4   '
GO


SELECT '"'+myName+'"', '"'+RTRIM(myName)+'"'
  FROM #myTable99
GO

DROP TABLE #myTable99
GO

Thanks, but these are string values. RTRIM does not truncate the returned field width to the length of the returned object name, which, again, CONVERT would do, provided the length is known beforehand.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources