Expand a field

Question

Post reply

Expand a field

scuggins

Valued Member

Points: 64
More actions
April 22, 2020 at 2:42 pm
Go to Answer
#3745324

Hi, I am trying to check the data in a column that is like ABC*1234 and expand it to look like ABC000001234.
I am new to sql and have basis experience.
Thanks
Dave

ScottPletcher

SSC Guru

Points: 101200
More actions
April 22, 2020 at 4:57 pm
Answer
#3745410
```
UPDATE dbo.table_name
SET data_column = REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))
WHERE (LEN(data_column_name) < 12 OR LEN(data_column_name) = 13) AND
    (data_column LIKE '%*%')
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

What is the requirement exactly?

Is it as simple as replacing an asterisk with five zeros, or do you want something which fills out the numeric part with leading zeros, to make it nine characters long, or something else?

scuggins Valued Member Points: 64 More actions · Answer 2

Hi,

The asterisk could represent 1 or more characters and I need to expand all of the entries in that column that are like the above to 12 characters, by padding the centre with zeros.

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 3

Jo Pattyn

SSC-Dedicated

Points: 32473

April 22, 2020 at 2:56 pm

#3745331

SELECT REPLACE('ABC*1234','*','00000') newvalue

frederico_fonseca SSCoach Points: 16251 More actions · Answer 4

SELECT REPLACE(oldvalue,'*', replicate('0', 12 - len(t.oldvalue) + 1)) newvalue
from (values 
         ('ABC*1')
        ,('ABC*123')
        ,('ABC*12345')
        ,('ABC*1234567')
        ,('ABC*12345678')
        ,('ABC12345678')
         ) t(oldvalue)

This reply was modified 5 years, 4 months ago by frederico_fonseca.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))

Some examples:

SELECT data_column,
    REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), '')) AS new_data
FROM ( VALUES('ABC*1234'),('BC*234'),('CDEFG*1'),('D*23456789012') ) AS test_data(data_column)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

scuggins Valued Member Points: 64 More actions · Answer 6

Scott,

I have run your code and it does what I require, as an addition how would I iterate through all records in the table checking the column and update to the new data value?

Thanks

Dave