Need Help Reg: Query - 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

General SQL Server Forums

New to SQL Server Programming

Need Help Reg: Query

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-11 : 05:12:35

hi friends,
my input is
001
001001
000000012
012
12001

i want ouput to be
1
1001
12
12
12001

datatype is varchar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 05:15:03

[code]DECLARE @Sample TABLE
(
Data VARCHAR(20)
)

INSERT @Sample
SELECT '001' UNION ALL
SELECT '001001' UNION ALL
SELECT '000000012' UNION ALL
SELECT '012' UNION ALL
SELECT '12001'

SELECT Data,
SUBSTRING(Data, PATINDEX('%[^0]%', Data), LEN(Data)) AS Peso
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-11 : 05:16:46

declare @a varchar(32)
select @a = '000000100000012'

select cast( @a as int)

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-11 : 05:22:59

thanks peso & Nageswar9

i may not be able to change as a int
i will go with peso

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:47:58

quote:
Originally posted by shaggy

thanks peso & Nageswar9

i may not be able to change as a int
i will go with peso

why? why cant you explicitly cast it as int in select? or even do this?


SELECT	Data,
	Data*1 AS IntVal
FROM	@Sample

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 03:48:23

There might be characters in the data?

E 12°55'05.63"
N 56°04'39.26"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-03-12 : 04:06:17

If you have a character you can't cast as int!

Better Use Substring function

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-12 : 04:41:09

as peso said there is a character in a data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:35:04

quote:
Originally posted by shaggy

as peso said there is a character in a data

then take those which have only numeric data

SELECT data*1
FROM Table
WHERE data NOT LIKE '%[A-Za-z]%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-13 : 05:51:11

quote:
Originally posted by visakh16

quote:
Originally posted by shaggy

as peso said there is a character in a data

then take those which have only numeric data

SELECT data*1
FROM Table
WHERE data NOT LIKE '%[A-Za-z]%'

WHERE data NOT LIKE '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources