SQL Syntax Help

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

Hello. I would like to update a field in a table by selecting the first 4
digits which match a pattern and update the field with replacement of 6
digits.

for example:

the number 1000009200000 would become 09001000920000

in this case the pattern is as follows:

1000 | 009200000

replaced as

0900100 | 009200000

I know it might go something like this:

update TableName,
set FieldName = Left,6,0900100
where FieldName = Left,4,1000

(or I'm way out to lunch ;-))

The Field is text.

Any help is very much appreciated.
 
Edward said:
Hello. I would like to update a field in a table by selecting the first 4
digits which match a pattern and update the field with replacement of 6
digits.

for example:

the number 1000009200000 would become 09001000920000

in this case the pattern is as follows:

1000 | 009200000

replaced as

0900100 | 009200000

I know it might go something like this:

update TableName,
set FieldName = Left,6,0900100
where FieldName = Left,4,1000

(or I'm way out to lunch ;-))

The Field is text.

Any help is very much appreciated.


In the case of Text column types:

CREATE TABLE MyTable_031004_1
(Column1 INTEGER
,Column2 CHAR(24)
,CONSTRAINT pk_MyTable PRIMARY KEY (Column1)
)

Sample Data:
1, 1000009200000

UPDATE MyTable_031004_1
SET Column2 = "0900100" & RIGHT(TRIM(Column2), 9)
WHERE LEFT(TRIM(Column2), 4) = "1000"
 
Thanks for that but I might have confused the issue with the pipe ( | ) in
my example, the pipe was to illustrate what I want to change vs. what I
want left alone, just all in the same column.

What I need is in one column, not two....
 
Edward said:
Thanks for that but I might have confused the issue with the pipe ( | ) in
my example, the pipe was to illustrate what I want to change vs. what I
want left alone, just all in the same column.

What I need is in one column, not two....

The change was in only one column, not two.
 
Back
Top