Sql server-Help me with a query

[jeenajos]

Hi all,

Im having a table with 7 lakhs record.TabIe have only one field.In that,each record start with a common set of number. I want to delete that common set of number from the entire table.

Eg:

123489574

123422147

123400789

In the above eg:1234 is the set of number.I want the result as

89574

22147

00789

Can anyone help me with the query?Pls...

Thank U in advance.

Jeen

[Jim Doherty]

Hi all,

Im having a table with 7 lakhs record.TabIe have only one field.In that,each record start with a common set of number. I want to delete that common set of number from the entire table.

Eg:

123489574

123422147

123400789

In the above eg:1234 is the set of number.I want the result as

89574

22147

00789

Can anyone help me with the query?Pls...

Thank U in advance.

Jeen

Are you bothered about retaining the leading zeros displayed? if so you will have to convert the numeric value to a varchar in order to display them at which point you lose the sortability of numeric because it is now displayed as text. Leading zeros numerically speaking are worthless you could not store them numerically for obvious reasons.

The following SQL will simply display for you five digits treated as text you where the table is called tbllakh and the field is called numbers

SELECT	 number, RIGHT('00000' + SUBSTRING(LTRIM(STR(number)), 5, LEN(STR(number))), 5) AS MyNewNumber 
FROM		 dbo.tbllakh

if you intend to actually 'update' your numeric data by removing the leading four digits then convert the number to a string firstly, find the postion of the fifth character and update the number field value with the digits from (and including) the fifth digit to the end of the character string but before doing so you must then convert those string characters back to an integer in order to update the numeric field called number in this case. Note you will lose the leading zeros in this case.

The following update will alter data and remove the leading 1234 you require. You must test it for yourself before you commit anything to your data.

UPDATE lakh
set number=CONVERT (int, SUBSTRING(LTRIM(STR(number)), 5, LEN(STR(number))))

Regards

Jim

[jeenajos]

Thank U so much for ur valuable information.

Jeen

[Jim Doherty]

Thank U so much for ur valuable information.

Jeen

You're welcome

Jim :)