SQLTeam.com | Weblogs | Forums

How to remove characters from a start of a string


#1

Hello,

I have the following string:

track22209826134608501000002"

How do I remove 'track' from the start of this?

What do I add to the following query (which I've already written to give me the above):

SELECT SUBSTRING([custom_data],charindex('track22',[custom_data]),28) AS Track2
FROM [dbo].[Sheet3$]
WHERE charindex('track22',[custom_data]) > 0

Thanks


#2

declare @yourColumn varchar(max)
set @yourColumn = 'track22209826134608501000002'

select Substring(@yourColumn,patindex('%[^a-zA-Z]%',@yourColumn),datalength(@yourColumn) )


#3

SELECT SUBSTRING([custom_data],charindex('track',[custom_data]),datalength(custom_data)) AS Track2
FROM [dbo].[Sheet3$]
WHERE charindex('track',[custom_data]) > 0


#4

Sorry I don't quite understand..how do I write it out? Do I write it as a new query?


#5

When you say "remove 'track'", do you mean that you want to remove the first four characters, or do you mean that you want to remove all leading non-numeric characters (regardless of how many such non-numeric characters there are), or something else?

If it is the first four characters you are trying to remove,

SELECT 
	STUFF(Track2,1,4,'') AS Track2
FROM
(
	SELECT  
		SUBSTRING([custom_data], 
			CHARINDEX('track22', [custom_data]), 28) AS Track2
	FROM
		[dbo].[Sheet3$]
	WHERE
		CHARINDEX('track22', [custom_data]) > 0
)s;

If you are trying to remove all the leading non-numeric characters, then this:

SELECT 
	STUFF(Track2,1,PATINDEX('%[0-9]%',Track2+'0')-1,'') AS Track2
FROM
(
	SELECT  
		SUBSTRING([custom_data], 
			CHARINDEX('track22', [custom_data]), 28) AS Track2
	FROM
		[dbo].[Sheet3$]
	WHERE
		CHARINDEX('track22', [custom_data]) > 0
)s;

If the rule for removing the leading characters is something else, please post the rule.