12,547,501 members (60,761 online)
Rate this:
See more:
The problem is I can't find ANY method to provide, SELECT query or PRINT or otherwise, a string of characters as a plain string of characters.

```USE[master]
GO

CREATE SCHEMA [DCST]

CREATE TABLE [DCST].[geoGeo](
[idx] [int] IDENTITY(1,1) NOT NULL,
[GeogCol1] [geometry] NULL,
[GeogCol2]  AS ([GeogCol1].[STAsText]())
) ON [PRIMARY]

INSERT INTO [DCST].[geoGeo](GeogCol1)
VALUES(geometry::STPointFromText('POINT(-73.57529478233685 45.50986635357582)',4326))
INSERT INTO [DCST].[geoGeo](GeogCol1)
VALUES(geometry::STPointFromText('POINT(149.1389139479469 -35.39418756346831)',4326))

SELECT [idx]
,[GeogCol1]
,[GeogCol2]
FROM [master].[DCST].[geoGeo]
GO
```

Sets up the data on [master]
```/*

idx	GeogCol1					GeogCol2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1	0xE6100000010C8FEC34A1D16452C02FF8F84C43C14640	POINT (-73.575294782336854 45.509866353575823)
2	0xE6100000010C7CECA9FB71A462400CCBF2BC74B241C0	POINT (149.13891394794689 -35.394187563468307)

*/
```

And the steps to understanding the problem is in the tests:

```DECLARE @r geometry
SET @r = (SELECT [GeogCol1] From [master].[DCST].[geoGeo] WHERE [idx] = 1)
PRINT CONVERT([nvarchar](1024),@r)						-- (messages) "POINT (-73.575294782336854 45.509866353575823)"
SELECT @r.STAsText()				        			-- POINT (-73.575294782336854 45.509866353575823)
SELECT @r.STAsBinary()								-- 0x01010000008FEC34A1D16452C02FF8F84C43C14640

DECLARE @rs geometry
SET @rs =  geometry::STGeomFromWKB(@r.STAsBinary(),0)
SELECT @rs.STAsText()								-- POINT (-73.575294782336854 45.509866353575823)
PRINT @rs.STAsText()								-- (messages) "POINT (-73.575294782336854 45.509866353575823)"
PRINT CAST(@rs.STAsText() As binary)						-- (messages) "0x50004F0049004E005400200028002D00370033002E003500370035003200"
SELECT CAST(@rs.STAsText() As binary)
DECLARE @binstr [nvarchar](MAX)
SET @binstr = (SELECT CAST(@rs.STAsText() As binary))
PRINT @binstr
```

Now, the thing is ... this end "@binstr"

-- (messages) "POINT (-73.5752" (why is this truncation occuring ? ... and collateral question, answer of which was the original question waylaid by this little discovery of a truncation here, how can I get a simple string of characters out of this binary reprentation known as geometry?!!)

(that linefeed of the comments makes this columnar stuff hard to read but please bear with it because the line merits explanation)
Posted 7-Dec-12 12:54pm
RedDk14.4K

Why?!
--SA

Rate this:

## Solution 1

Figured out why the truncation is occuring. Here posting the results as the answer to the problem; my use of [nvarchar] to contain data of type [varbinary].

As an example:

```SELECT CAST('0x50004F0049004E00' As binary)															-- 0x307835303030344630303439303034453030000000000000000000000000  ( ascii code: "0x50004F0049004E00NULNULNULNULNULNULNULNULNULNULNULNUL")
SELECT CAST('0x50004F0049004E005400200028002D003100320032002E00340031003500' As binary)				-- 0x307835303030344630303439303034453030353430303230303032383030
SELECT CAST('0x50004F0049004E00' As [nvarchar](128))												-- 0x50004F0049004E00
SELECT CAST('0x50004F0049004E005400200028002D003100320032002E00340031003500' As [nvarchar](MAX))	-- 0x50004F0049004E005400200028002D003100320032002E00340031003500
SELECT CAST(CAST(@rs.STAsText() As binary) As [nvarchar](MAX))										-- POINT (-134.00
SELECT CAST(CAST(@rs.STAsText() As binary) As binary)												-- 0x50004F0049004E005400200028002D003100320032002E00340031003500
DECLARE @regstr [nvarchar](MAX)
SET @regstr = CAST(CAST(@rs.STAsText() As binary) As binary)
SELECT @regstr As [binaryStr]
/* and the conceptual impasse solution */
SELECT CAST(CAST(@rs.STAsText() As binary) As binary) As [binaryStr]								--
/*
binaryStr
0x50004F0049004E005400200028002D003100320032002E00340031003500
*/
SELECT CAST(CAST(@rs.STAsText() As binary) As [varbinary](MAX))        --  0x50004F0049004E005400200028002D003100320032002E00340031003500
```

(In order to better see the code in these PRE tags click on the "Copy Code")

So, in conclusion ... I CAN get the binary string @binstr by using the right type(d'oh)!, [varbinary](MAX). The operation in the SELECT clause returning the abbreviation as an unknown conversion is some sort of default behavior. Unexpected but not necessarily as wrong as redtext error.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/65353777-e3e6-461c-9504-ea4d83312692.htm
v3

Top Experts
Last 24hrsThis month
 OriginalGriff 255 Suvendu Shekhar Giri 255 Mika Wendelius 139 Maciej Los 130 Rbabs 80
 OriginalGriff 3,827 John Simmons / outlaw programmer 1,687 Suvendu Shekhar Giri 1,633 ppolymorphe 1,441 Karthik Bangalore 1,170