There are a couple of points in the stuff below. One is the value of having sensible names. Perfectly nice people put spaces into file names or urls. Or mix numbers and letters (like putting a ‘z’ in from of something to indicate not to use that number.) Just because they can. There then is need to deal with such-like. Or make EVERYTHING varchar(max) And a quick but dirty(?) way to create a numbers table. This one i can even remember.
From Simon Sabin – he wants to parse data into into urls, but the data contains spaces and other naughty bits – which means URL encoding (space becomes %20, et al). What he does is select the data (which are session titles) and replace spaces with an underscore character: “big cat” -> ‘big_cat’.
select cast(cast(( select case when substring(title,n,1) like '[a-z0-9/-]' then substring(title,n,1) else '_' end from num where n <= len(title) for xml path('')) as xml) as varchar(max))
So get the substring of one character, check to see if it is appropriate (is this all lowercase?) , if so select it, if not replace with ‘_’. This assumes a numbers table/tally table of your choice.
From Derek Dieter, he has some strings that might contain non-numeric things (123A456) and he was to get rid of the ‘A’ and convert 123456 into an integer. Similar to the above, he does this
SELECT CAST ( ( SELECT CASE WHEN SUBSTRING(field,Num,1) LIKE '[0-9]' THEN SUBSTRING(field,Num,1) ELSE '' END FROM Numbers WHERE Num <= LEN(field) FOR XML PATH('') ) AS INT ) FROM inputTable
It depends on how long the field is if the maxrecursion is needed – the default is 100 and this applies since we are using a CTE.
The rest of the code is generating the numbers table. He does that like this
DECLARE @MaxNumber INT = 5000 ;WITH Numbers AS ( SELECT 1 AS Num UNION ALL SELECT Num+1 FROM Numbers WHERE Num <= @MaxNumber )
Select 1, union all and do it again with select Num + 1. Repeat until cooked. Actually, repeat as many of 32767 numbers. As above for the default recursion of 100, this will not work with @maxNumber = 500. It will work with 100, or you can set the OPTION(MAXRECURSION 5000) . Or leave it at 32767 and don’t worry until it gives you an error
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.