June 15, 2016 at 3:16 pm
The Select stmt returns many rows but one column.
I need to feed this value to using the
master.dbo.fn_varbintohexstr function so that my select stmt will
only return a hex value
So help plese
It sould be like
Select master.dbo.fn_varbintohexstr( ....? )
FROM
information_schema.columns WHERE TABLE_NAME = 'MCNNPIMapping'
Select
'['+Table_Catalog+']'
+'['+Table_Schema+']'
+'['+Table_Name+']'
+'['+Column_Name+']'
+'['+CAST(ORDINAL_POSITION as varchar ) +']'
+'['+ISNULL(COLUMN_DEFAULT,'')+']'
+'['+ISNULL(IS_NULLABLE,'')+']'
+'['+ISNULL(DATA_TYPE,'')+']'
+'['+CAST( ISNULL(CHARACTER_MAXIMUM_LENGTH,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_OCTET_LENGTH,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_PRECISION,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_PRECISION_RADIX,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_SCALE,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(DATETIME_PRECISION,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_CATALOG,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_SCHEMA,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_NAME,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_CATALOG,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_SCHEMA,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_NAME,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_CATALOG,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_SCHEMA,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_NAME,'') as VARCHAR ) +']'
FROM
information_schema.columns WHERE TABLE_NAME = 'MCNNPIMapping'
June 15, 2016 at 5:52 pm
You don't need to use that function. You can do it like this:
Select CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),CAST(
'['+Table_Catalog+']'
+'['+Table_Schema+']'
+'['+Table_Name+']'
+'['+Column_Name+']'
+'['+CAST(ORDINAL_POSITION as varchar ) +']'
+'['+ISNULL(COLUMN_DEFAULT,'')+']'
+'['+ISNULL(IS_NULLABLE,'')+']'
+'['+ISNULL(DATA_TYPE,'')+']'
+'['+CAST( ISNULL(CHARACTER_MAXIMUM_LENGTH,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_OCTET_LENGTH,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_PRECISION,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_PRECISION_RADIX,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(NUMERIC_SCALE,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(DATETIME_PRECISION,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_CATALOG,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_SCHEMA,0) as VARCHAR ) +']'
+'['+CAST( ISNULL(CHARACTER_SET_NAME,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_CATALOG,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_SCHEMA,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(COLLATION_NAME,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_CATALOG,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_SCHEMA,'') as VARCHAR ) +']'
+'['+CAST( ISNULL(DOMAIN_NAME,'') as VARCHAR ) +']'
AS VARCHAR(MAX))
),1)
FROM
information_schema.columns WHERE TABLE_NAME = 'MCNNPIMapping'
June 16, 2016 at 7:06 am
Good!
At the same time how would I decrypt the hex value back to normal text
The good thing about using the master.dbo.fn_varbintohexstr is that you are
able to directly insert that value to a table and sql server will implicitly convert it into text.
June 16, 2016 at 7:18 am
To convert back do this
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), '0x53514C205365727665722043656E7472616C', 1))
The original query above can be inserted into a table no problem. I don't see anything good about that function. it will be slow and its not supported by Microsoft.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply