CODEWITHSUNDEEP
c#sql-server
LimpSquid
LimpSquid

Reputation: 327

SQL server: replace characters from string within a specified range

I'm having a question regarding replacing characters on a specific location in a string. My c# application has the ability to read files such as: TXT, CSV, Excel, database, etc. and import them to a SQL server on the network (the user is able to choose to which server we import the files). The application is ment to compare the two imported files with eachother. To improve the comparison between the two tables I want to be able to replace some specific characters. To give you an example. One column in the first imported file has part numbers without any special characters. The second imported file also have a column with the same part numbers as the first imported file does, only those partnumbers are seperated with a dot on every third character. To improve the search I remove ALL the dots from the second imported file. This can easily be done with a REPLACE transact. The query (that is exectuded from my c# application, I don't want to make a stored procedure because the server can be changed by the user's choice) will look like this:

UPDATE myTable SET myColumn = REPLACE(myColumn , '.', '');

This replace statement is working just fine. However the hard part I want to achieve. Lets say a column in the first imported file has partnumbers that looks like this:

132105213.000
452993424.001
436345332.002
etc...

And the second imported file has the same partnumbers only they look like this:

132.105.213.000
452.993.424.001
436.345.332.002
etc...

To improve the comparison between those two column I only want to remove the FIRST TWO dots and leave the third dot. So the REPLACE transact should only be executed on character 4 to 8. Is there any way to do this on the server side?

Some things to consider:

If more info needed place a comment below so I can edit my question!

Upvotes: 1

Views: 2910

Answers (3)

LimpSquid
LimpSquid

Reputation: 327

The answers you provided helped me with figuring out the solution, atleast I think it is a good solution? This is how the query looks like:

UPDATE myTable
SET myColumn = STUFF(myColumn, fromCharPosition, toCharPosition, REPLACE(SUBSTRING(myColumn, fromCharPosition, toCharPosition, charToReplace, charReplacement));

So the query will look like this for the example I made in my question:

UPDATE myTable
SET partNumber = STUFF(partNumber, 4, 8, REPLACE(SUBSTRING(partNumber, 4, 8), '.', ''));

Again thanks for helping out this trainee!

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try this. It will leave the last period

DECLARE @t table(val varchar(50))
INSERT @t values
('132.105.213.000'),
('452.993.424.001'),
('436.345.332.002'),
('123')

SELECT replace(left(val, len(val) - len(rightval)), '.', '') + rightval
FROM @t t
OUTER APPLY
(SELECT right(val, charindex('.',reverse(val))) rightval) x

Upvotes: 1

GarethD
GarethD

Reputation: 69789

I don't understandand your adversity to STUFF, the following seems like it would work fine:

DECLARE @Start INT = 4,
        @End INT = 9,
        @Replace NCHAR(1) = '.';

SELECT  s = STUFF(t.String, 
                @Start,
                @End - @Start,
                REPLACE(SUBSTRING(t.String, @Start, @End - @Start), @Replace, '')
                )
FROM    (VALUES
            ('132.105.213.000'),
            ('452.993.424.001'),
            ('436.345.332.002'),
            ('132105213.000'),
            ('452993424.001'),
            ('436345332.002')
        ) AS t (String);

Basically you are extracting the string between the specified characters (SUBSTRING(t.String, @Start, @End - @Start)), then performing the replace on this extract, and stuffing what is left back into the original string.

Upvotes: 1

Related Questions