Error Converting Data Type Varchar to Float

1

Hello all.

So I have this whacky string to extract 6 numbers from a long INT field.
REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,18,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),'')

But, when there are only 5 digits, I want to pad it with a zero. So I used:
Replace(STR(REPLACE(REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,18,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),''),6),'','0')

But I get:
Error converting data type varchar to float

This field is full of alpha and numeric characters, which I am pretty sure is causing the error.

image
21 06/21/2021 23 21808 22 PAYPAL *LANDSENDIN 4029357733 WI 20 24492151172894870595901 241 0 X 1 44.95
The desired result would be the highlighted area ... with a zero in front of the first one (021808)

Any thoughts on how to get around this? Thanks.

2

please provide sample TokenString data and what you want it to look like after conversion. just posting an error will not help us help you

3

Please provide the data not as an image. We would have to type it up on top of trying to help you.

Help us help you. Post the data in a way we can at least copy paste it locally

4 
SELECT RIGHT('000000' + REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(
    SUBSTRING(ah.TokenString,18,6)),CHAR(0x09),''),CHAR(0x04),''),CHAR(0X05),''), 
    CHAR(32), ''), 6)
5

Boom. You are a like a SQL Ninja Master! Appreciate it!

6

Well, actually Scott IS kinda SQL Ninja Master :slight_smile: