Access query translate to SQL Server

[ChrisElias271]

Hi All,

I'm desperately trying to use an old access query and translate it to
be used in SQL enterprise manager.


The dataset i'm trying to update is too large for access (28 million
rows).


IIf([TableName]![FIELDNAME] Like "??0*",Left([TableName]![FIELDNAME],
2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME]
Like
"?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]!
[FIELDNAME],
4),[TableName]![FIELDNAME]))


This works to remove padding zeros in the middle of a postcode field.


eg


SR04 PQJ would be SR4 PQJ
or
S03 4PY would be S3 4PY.


It does this by finding all postcodes that begin with either ??0 or ?
0
and then recompiles them using concatenation.


This works fine in access but i can't translate it to be read in SQL
server. I'm aware of the differences in wildcard chars and the CONCAT
function but cannot seem to get it right.


Could someone please help!!

 

[Bob Barrows [MVP]]

Hi All,

I'm desperately trying to use an old access query and translate it to
be used in SQL enterprise manager.


The dataset i'm trying to update is too large for access (28 million
rows).


IIf([TableName]![FIELDNAME] Like "??0*",Left([TableName]![FIELDNAME],
2) & Right([TableName]![FIELDNAME],4),IIf([TableName]![FIELDNAME]
Like
"?0*",Left([TableName]![FIELDNAME],1) & Right([TableName]!
[FIELDNAME],
4),[TableName]![FIELDNAME]))


This works to remove padding zeros in the middle of a postcode field.


eg


SR04 PQJ would be SR4 PQJ
or
S03 4PY would be S3 4PY.


It does this by finding all postcodes that begin with either ??0 or ?
0
and then recompiles them using concatenation.

To replace all 0s, do this:

REPLACE(FIELDNAME,'0','')

To only replace the ones in the first 3 characters:

REPLACE(Left(FIELDNAME,3),'0','') + SUBSTRING(FIELDNAME,4,20)