Convert String to Hex and Concatenate with

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

Convert String to Hex and Concatenate with

Author

Topic

ggalliga
Starting Member

2 Posts

Posted - 2014-09-04 : 17:02:50

To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)

However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.

ex. Field 1 + Field 2(hex string) = Field 3
Field 1 = 'FF02324323'
Field 2 = 'Smith Creek'
Field 3 = 'FF02324323536D69746820437265656B'

Field 1 VarChar(10) (Code)
Field 2 VarChar(65) (Common Name)
Field 3 VarChar(max) (ResourceID)

Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need. Seems like it should be simple...

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-04 : 17:27:32

Maybe:

select field1
      +right(master.sys.fn_varbintohexstr(cast(field2 as varbinary))
            ,len(master.sys.fn_varbintohexstr(cast(field2 as varbinary)))-2
            )
  from yourtable

ggalliga
Starting Member

2 Posts

Posted - 2014-09-04 : 18:06:21

Thank you very much. That worked beautifully. Was going to post a second question on how to actually add the values to Field 3 but I figured that out.

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-05 : 02:19:34

Function fn_varbintohexstr is unsupported.
Use CONVERT instead.

DECLARE	@Sample TABLE
	(
		Col1 VARCHAR(100) NOT NULL,
		Col2 VARCHAR(100) NOT NULL,
		Col3 VARCHAR(300) NULL
	);

INSERT	@Sample
	(
		Col1,
		Col2
	)
VALUES	('FF02324323', 'Smith Creek');

-- SwePeso
SELECT	Col1,
	Col2,
	Col1 + CONVERT(VARCHAR(200), CAST(Col2 AS VARBINARY(100)), 2)
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources