Rtrim... ish

B@Th*nG · 13 Jun 2012 at 12:01

hi guys,

i've got some data i want to apply an rtrim to, but it doesn't necessarily have spaces at the end of the field. example:

Code:

Celebrations                                   [D]

How can i remove all the annoying spaces and keep the "[D]", or whatever it is, at the end?

B@

GravyMonster · 13 Jun 2012 at 12:16

You can use a regex to replace multiple spaces with one, here's a C# example:

Code:

string test = "Celebrations                                   [D]";
test = Regex.Replace(test, @"\s+", " ");

B@Th*nG · 13 Jun 2012 at 12:19

perfect, any idea how you'd write that for access or SQL server?

B@

GravyMonster · 13 Jun 2012 at 12:24

It would probably be a lot more convoluted for those as I don't think you can use regular expressions there.

B@Th*nG · 13 Jun 2012 at 14:53

ok fair enough, anyone else have any ideas?

B@

dasyad · 13 Jun 2012 at 15:24

If you're using SQL Server and C# then you can write an assembly that calls the C# regex functions, deploy it to the SQL Server and then expose it via a SQL scalar-valued function.

So if you have SSMS installed you want to be looking at the Datbase\Programmability\Functions and Datbase\Programmability\Assemblies folders.

[EDIT]Just found what we use:

Code:

    [Microsoft.SqlServer.Server.SqlFunction]
    public static bool IsRegexMatch(string Input,string Pattern)
    {
        // Put your code here
        Regex reg = new Regex(Pattern);

        return reg.IsMatch(Input);
    }

So we have a C# assembly with that function in, we published the assembly to a SQL Server database and then created a SQL function that basically points to that assembly function.

You might want to put some error checking in there as it will throw an exception if you give it a NULL string

[EDIT2]Just realised that just checks for a match but doesn't actually do a replacement. I'm sure you get the idea that you could use Spunkeys code in one of these functions and then call that

Jamie · 13 Jun 2012 at 19:22

SQL Server would be something like the following surely?

Code:

SELECT replace( @Data, ' ', '' )

B@Th*nG · 13 Jun 2012 at 21:49

Jamie said:
SQL Server would be something like the following surely?

Code:

SELECT replace( @Data, ' ', '' )

that would replace all spaces wouldn't it?

B@

mp3duck · 14 Jun 2012 at 08:23

probably not the best solution, but the following should work:

PHP:

declare @myval nvarchar(100)
set @myval = 'Celebrations                                       [D]'
select replace(replace(replace(replace(replace(@myval,'     ',' '), '    ', ' '), '   ',' '),'  ',' '), '  ',' ')

Tried with various lengths of spacing, and it seems to work.

Jamie · 14 Jun 2012 at 20:28

B@Th*nG said:
that would replace all spaces wouldn't it?

B@

Sure, I'm guessing that's not what you want then? How common are the last few characters? You may want to write a stored procedure that removes the last 3 chars, then trims and replaces the last 3 chars.

B@Th*nG · 15 Jun 2012 at 14:10

mp3duck said:
probably not the best solution, but the following should work:

PHP:

declare @myval nvarchar(100) set @myval = 'Celebrations [D]' select replace(replace(replace(replace(replace(@myval,' ',' '), ' ', ' '), ' ',' '),' ',' '), ' ',' ')

Tried with various lengths of spacing, and it seems to work.

this works perfectly, thanks very much!!

B@

mp3duck · 15 Jun 2012 at 14:21

B@Th*nG said:
this works perfectly, thanks very much!!

B@

np. pleased it worked for you!