SQLTeam.com | Weblogs | Forums

USING SUBSTRING AND CAST......Grrrrr!


#1

Someone help me before this pc goes through the window :disappointed:

falls over with

I've mixed and matched brackets just about everywhere to no avail. Any pointers appreciated.


#2

Your first argument to SUBSTRING is an INT. You can't use an INT at that point, it needs to be a string.

If you are trying to get the left-most 10 digits from very large numbers this is probably not the best way to do it ... but maybe you were trying to do something different - like take the leftmost 10 CHARACTERS (which might happen to be numeric digits) from a long string?


#3

20130502_427.28_A_CREATE is a typical example of the data. I know that the first 9 characters are superfluous as are the last 9 so I'm just trying to extract the middle (in this case) 6 although the number is variable.


#4

I moved the parenthesis for the LEN SELECT @vx = SUBSTRING (CAST (MinType AS INT), 10, LEN (MinType) - 18) FROM [dbo].[usethistable] WHERE ID = @uid


#5
; WITH CTE AS
(
	SELECT [MyColumn] = '20130502_427.28_A_CREATE'
	UNION ALL SELECT '123456789XaaaaaY987654321'
)
SELECT	SUBSTRING(MyColumn, 10, LEN(MyColumn)-18)
FROM	CTE

#6

Nicely spotted - pretty poor syntax on my part to begin with :flushed: