replace and convert

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
OPTION(MAXRECURSION 32767)

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.
Advertisements