Replace value from

Question

Post reply

Replace value from

pedroccamara

SSC-Addicted

Points: 431
More actions
October 31, 2022 at 5:58 pm
Go to Answer
#4106909

Hi guys
I hope everything's ok with all of you
I have this example to do it in SQL. I want all characters after the space between. like this
Please beware the number of characters. The ideal would be something like "give all characters from space to the right"
I hope I was clear.
Thanks a lot in advance

Jonathan AC Roberts

SSCoach

Points: 18354

October 31, 2022 at 9:09 pm

Answer

#4106985

Ah, I see, test driven development:

;with cte as
(
    select * from (values ('Restaurant'),
                          ('Restaurant Abc1'),
                          ('Rest Abc'),
                          ('Re Abc32'),
                          ('Restau Abc4'),
                          ('Restaurant Abbe'))T(C)
)
select C , right(C, len(C) - charindex(' ', C)) 
from cte

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

without giving you the code look at functions charindex, substring and reverse(use twice) - using these 3 you will be able to get that split

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

Ok Frederico. I got this

SUBSTRING (Restaurant, CHARINDEX ('- ', Restaurant), 20) AS Restaurant

The result is this

- RestaurantName

I don't think I understood that REVERSE function because from what I've seen the result would be

emaNtnaruatseR -

I'm missing something, right?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

frederico_fonseca

SSCoach

Points: 16154

October 31, 2022 at 6:57 pm

#4106942

you missing the fact I said reverse (use twice)

drew.allen SSC Guru Points: 76985 More actions · Answer 4

frederico_fonseca wrote:

without giving you the code look at functions charindex, substring and reverse(use twice) - using these 3 you will be able to get that split

I believe that reverse is relatively expensive. I think it's simpler--and therefore easier--to use just charindex and stuff.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

multiple ways - I was just giving one of the ways with an incentive to search for it and try to understand it.

and... substring vs stuff - which one is better performance? not sure if there are any testcases on that.

one thing that may mess up with Any of the ways is if there are cases where the string has more than 1 space (e.g. 3 words or more).

What should happen on that case Pedro

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

pedroccamara wrote:

Hi guys I hope everything's ok with all of you
I have this example to do it in SQL. I want all characters after the space between. like this
Please beware the number of characters. The ideal would be something like "give all characters from space to the right" I hope I was clear. Thanks a lot in advance

Two things...

What do you want to do if there's only one word (and, yes, it will happen)? What about more than 2 words?
You'd probably get some coded answers if you'd post some readily consumable data instead a a graphic. See the article at the first link in my signature below for more information on the WHY and the HOW.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pedroccamara SSC-Addicted Points: 431 More actions · Answer 7

Hi Frederico,

I haven't missed that fact of using twice the REVERSE function but look at what I've said:

I wrote this: SUBSTRING (Restaurant, CHARINDEX ('- ', Restaurant), 20) AS Restaurant

and what I got was this

'- RestaurantName'

Even If I've used the REVERSE twice it wouldn't change what I'm looking at here...unless it should be used differently. And that's the help I ask you because I don't have enough knowledge to use that function properly.

Also, to answer your question, it's always just 2 words separated by a space dash space, like my example

Jonathan, I'm going to try your suggestion. I don't think it will work because it should be splited after ' - ' always and show me everything after that space dash space

Thanks a lot all of you for helping me

Still looking forward for the right solution

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Using Jonathan's test data, here is the STUFF() version.

WITH cte
AS (SELECT *
    FROM
    (
        VALUES
            ('Restaurant')
           ,('Restaurant Abc1')
           ,('Rest Abc')
           ,('Re Abc32')
           ,('Restau Abc4')
           ,('Restaurant Abbe')
    ) T(C) )
SELECT cte.C
      ,STUFF (cte.C, 1, CHARINDEX (' ', cte.C), '')
FROM cte;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

pedroccamara wrote:

Jonathan, I'm going to try your suggestion. I don't think it will work because it should be splited after ' - ' always and show me everything after that space dash space

Your example data does not include any hyphens. Please give us examples of what it really looks like.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 10

Hi guys,

I've just found the right solution based on Jonathan's idea

Restaurant , RIGHT(Restaurant, len(Restaurant) - charindex(' - ', Restaurant)-2) AS C

Thank you all so much, really