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

[rasta_pickles]

Someone help me before this pc goes through the window

falls over with

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

[Kristen]

@rasta_pickles

Argument data type int is invalid for argument 1 of substring function

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?

[rasta_pickles]

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.

[djj55]

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

[Kristen]

@rasta_pickles

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.

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

[rasta_pickles]

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