column value help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

column value help

Author

Topic

tdog
Starting Member

3 Posts

Posted - 2007-10-09 : 21:29:27

I have a column with values that might end with an alpha character. Example 5120001A. If the last digit of the code is an alpha, I wish to strip it off. If its numeric, ignore. ANy quick, easy script to do this ? thanks

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-09 : 21:53:07

This might help you out:
http://www.nigelrivett.net/RemoveNonNumericCharacters.html

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 22:28:03

[code]
DECLARE @TestData TABLE
(
MyColumn varchar(10)
)

INSERT INTO @TestData
SELECT '5120001A' UNION ALL
SELECT '5120002'

SELECT ']' + MyColumn + '['
FROM @TestData

UPDATE U
SET MyColumn = LEFT(MyColumn, DATALENGTH(MyColumn)-1)
FROM @TestData AS U
WHERE MyColumn LIKE '%[A-Za-z]'

SELECT ']' + MyColumn + '['
FROM @TestData
[/code]
Kristen

tdog
Starting Member

3 Posts

Posted - 2007-10-10 : 00:12:22

quote:

Originally posted by Kristen


DECLARE @TestData TABLE
(
	MyColumn varchar(10)
)

INSERT INTO @TestData
SELECT	'5120001A' UNION ALL
SELECT	'5120002'

SELECT	']' + MyColumn + '['
FROM	@TestData

UPDATE	U
SET	MyColumn = LEFT(MyColumn, DATALENGTH(MyColumn)-1)
FROM	@TestData AS U
WHERE	MyColumn LIKE '%[A-Za-z]'

SELECT	']' + MyColumn + '['
FROM	@TestData

Kristen

tdog
Starting Member

3 Posts

Posted - 2007-10-10 : 00:13:20

quote:

Originally posted by tdog

quote:

Originally posted by Kristen


DECLARE @TestData TABLE
(
	MyColumn varchar(10)
)

INSERT INTO @TestData
SELECT	'5120001A' UNION ALL
SELECT	'5120002'

SELECT	']' + MyColumn + '['
FROM	@TestData

UPDATE	U
SET	MyColumn = LEFT(MyColumn, DATALENGTH(MyColumn)-1)
FROM	@TestData AS U
WHERE	MyColumn LIKE '%[A-Za-z]'

SELECT	']' + MyColumn + '['
FROM	@TestData

Kristen

PERFECT! thank you very much, Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-10 : 02:02:36

quote:

Originally posted by Kristen


DECLARE @TestData TABLE
(
	MyColumn varchar(10)
)

INSERT INTO @TestData
SELECT	'5120001A' UNION ALL
SELECT	'5120002'

SELECT	']' + MyColumn + '['
FROM	@TestData

UPDATE	U
SET	MyColumn = LEFT(MyColumn, DATALENGTH(MyColumn)-1)
FROM	@TestData AS U
WHERE	MyColumn LIKE '%[A-Za-z]'

SELECT	']' + MyColumn + '['
FROM	@TestData

Kristen

Provided only last character can be alphabet, this may also work


select 	case 
		when 
			isnumeric(MyColumn)=0 then LEFT(MyColumn, DATALENGTH(MyColumn)-1) 
		else 
			mycolumn 
	end
from @TestData

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 04:15:49

Is LEN() faster than DATALENGTH()?

Given that we know that the last char is NOT space then LEN() would be OK here.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-10 : 04:24:54

quote:
Originally posted by Kristen

Is LEN() faster than DATALENGTH()?

Given that we know that the last char is NOT space then LEN() would be OK here.

I think so until some TESTs prove it wrong

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 04:53:49

It may have to do with whether the Target is Varchar or Nvarchar - all that shuffling in-and-out of Unicode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources