CODEWITHSUNDEEP
sqlsql-updateteradata
staples
staples

Reputation: 424

SQL - Update multiple values at one position

The title isn't as descriptive as I want it to be. Here is my task at hand: I have 17 numbers and each follow this format 12345-67 (i.e. 06037-11) What I need my numbers/strings to be is 123456-78. In place 6, I am inserting a 0.

To find these numbers I used this query:

select number
 from schema.table
 where number like '%-%'
 and length(trim(number)) > 1
 and length(substr(number, 1, position('-' in policy_number))) < 7
 order by policy_number;

How can I update them all in a similar manner without doing each individually?

edit: I am using Teradata

Examples of records:

06037-11
24188-18
25559-18

Upvotes: 0

Views: 93

Answers (2)

sgeddes
sgeddes

Reputation: 62861

Here's one option using the _ wildcard character which matches on any single character:

update yourtable 
set number = replace(number, '-', '0-')
where number like '_____-__'

This will only update records where there are 5 characters dash 2 characters.

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

something like 

update schema.table
set number = substr(number,1,5) + '0' + substr(number,6,4)
 where number like '%-%'
 and length(trim(number)) > 1
 and length(substr(number, 1, position('-' in policy_number))) < 7

Upvotes: 1

Related Questions