SQLTeam.com | Weblogs | Forums

Error Converting Data Type Varchar to Float

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.

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

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

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

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

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