Need help with creating a query to run in enterprise manager

[DivinDave]

Any help with this would be greatly appreciated. I need to create a query and my SQL skills are very very rusty.

Lets say I have the following;
Database name = users
Table name = info1
Table type is NVARCHAR 40
Info1 can contain the following type of data;
<NULL>, all numbers ie.. 1111222233334444, or alphanumeric ie.. JAMISON/ JOHN8

Here is my challenge. I need an update query to go through every record and if it is of type "all numbers" ie.. 1111222233334444, I want to keep the last 4 numbers and replace the rest with 0. So the resulting record would be 0000000000004444.

Can one of you SQL genius's help me with this. I'm stumped.

[Delerna]

Try checking the SQL help files index for the function ISNUMERIC
That should get you started.

[DivinDave]

Try checking the SQL help files index for the function ISNUMERIC
That should get you started.

'Any other help? I REAlly am rusty and neeed a lot of help. THANKS!

[ck9663]

Any help with this would be greatly appreciated. I need to create a query and my SQL skills are very very rusty.

Lets say I have the following;
Database name = users
Table name = info1
Table type is NVARCHAR 40
Info1 can contain the following type of data;
<NULL>, all numbers ie.. 1111222233334444, or alphanumeric ie.. JAMISON/ JOHN8

Here is my challenge. I need an update query to go through every record and if it is of type "all numbers" ie.. 1111222233334444, I want to keep the last 4 numbers and replace the rest with 0. So the resulting record would be 0000000000004444.

Can one of you SQL genius's help me with this. I'm stumped.

try:

update info1
set YourColumn = right(replicate('0',16) + right(YourColumn,4),16)
where isnumeric(YourColumn) = 1
 

Just replace the column name.

-- CK