string pattern function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

string pattern function

Author

Topic

havey
Starting Member

16 Posts

Posted - 2009-01-29 : 19:37:41

Hi
i have data in a column like so: 0x54583D302E3435

here is a sample from 3 records

0x54583D302E3435
0x54583D322E3235
0x54583D302E3639

I'm not sure what "encoding" is used but i figured out that the decimal values that these represent are in a pattern. For example

0x54583D302E3035=0.0
0x54583D352E3133=5.1

here is the first one broken down:
0x54583D3 0 2E3 0 35=0.0

the second:
0x54583D3 5 2E3 1 33

after this part: 0x54583D3 the whole number exists until
2E3 then the decimal exists.

Question: does anyone know the excoding this is

or how would a function look to extract these numbers, here is my ill attempt, thanks

DECLARE @Value NVARCHAR(200)
DECLARE @FirstSet NVARCHAR(6)
DECLARE @SecondSet NVARCHAR(6)

SET @Value = '0x54583D302E3035'

SET @FirstSet = REPLACE('0x54583D3', SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')

SET @SecondSet = REPLACE('0x54583D3'+FirstSet, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')

SET @Value = @FirstSet + '.' + @SecondSet

SELECT @Value

Raibeart
Starting Member

8 Posts

Posted - 2009-01-30 : 10:48:03

What is the data type of he original column?

kseabolt
Starting Member

2 Posts

Posted - 2009-01-30 : 12:19:13

These look like varbinary data to me. Try this:

select convert(varchar(7),0x54583D302E3435)
select convert(varchar(7),0x54583D322E3235)
select convert(varchar(7),0x54583D302E3639)

Results:

TX=0.45
TX=2.25
TX=0.69

Do those values make any sense to you?

havey
Starting Member

16 Posts

Posted - 2009-01-30 : 19:06:20

thansk kseabolt, right you are.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources