tsql convert to bigint after cleanup unwanted characters

Question

I want to clean up the below rows of data into bigint:

"0000000008/314282" 
"00000000  6058829" 
"00000000 80239283" 
"0000000  80272516" 
"000000000 5428588" 
"00000000 80328516" 
"00000000 81597613" 
"000000008159*2693" 
"00000000 81585289" 
"00000000 81601382" 
"00000000 81727962" 
"00000000101*79919" 
"0000000  81607533" 
"0000000  81605092" 
"0000000  81606197" 
"000000000 7376759" 
"0000000  81667128"

you will notice some have slashes"/" in between while some has asterisk "*" while some have white spaces in between.

I would like a function or technique to write up in query to clean all the rows with these unwanted characters(/*whitespaces) into a bigint.

score 1 · Answer 1 · answered Jan 14 '18 at 04:29

If what you need is to replace all the Non-Numeric Characters of your string and make it a Numeric Value, try the following

DECLARE @Num VARCHAR(20) = '977C2CSDC132/*'
;WITH CTE
AS
(
    SELECT
        SeqNo = LEN(@Num),
        MyNum = CAST(@Num AS VARCHAR(20)),
        NumLen = LEN(@Num),
        PrevChar = 1

    UNION ALL
    SELECT
        SeqNo = SeqNo-1,
        MyNum = CAST(
                CASE WHEN ISNUMERIC(SUBSTRING(MyNum,PrevChar,1))=1
                    THEN MyNum
                ELSE REPLACE(MyNum,SUBSTRING(MyNum,PrevChar,1),'') END
                AS VARCHAR(20)),
        NumLen = NumLen,
        PrevChar = CASE WHEN ISNUMERIC(SUBSTRING(MyNum,PrevChar,1))=1
                        THEN PrevChar+1
                    ELSE PrevChar END
        FROM CTE
            WHERE SeqNo >0
)   
SELECT
    MyNum = CAST(MyNum AS BIGINT)
    FROM CTE
        WHERE SeqNo = 0

this is for only one particular record. how can I fit in to run It for all the records? — Moshin Khan, Jan 14 '18 at 04:39
in the First Select, instead on selecting the Variable, select the Column from your table — Jayasurya Satheesh, Jan 14 '18 at 07:44

tsql convert to bigint after cleanup unwanted characters

1 Answers1