Solved Find characters and remove them

[Galaxiom]

For instance, I am really good at loops and if statements in Access VBA but I have to get good at stored procedures and IF statements in tsql.

I just learned about IF OBJECT_ID(db_name) BEGIN...END and I need to learn more about the other metadata function but here I think I am going to create a stored procedure that will take a field as a parameter and if a value in that field ends with 'R', it will change its value by removing the R.
This is where I am and I am working on finding a solution that works.

Sounds like you are trying to port inefficient VBA techniques you employed in Access to SQL Server. Learn the mantra "USE THE ENGINE".

You are complicating matters unnecessarily. A query like arnelgp posted should do the job. If it doesn't then you are missing something.

 

[Galaxiom]

UPDATE dbo.table_name SET field_name = REPLACE(REPLACE(field_name, 'POS', ''), 'R', '')

I'll add an unimportant detail that will save you a lot of typing over time. dbo is the default schema and can be omitted.

When referring to another database too.

databasename.dbo.tablename.columnname

can be referred to as

databasename..tablename.columnname

Doesn't work for referring to functions though.

 

[Isaac]

You are complicating matters unnecessarily. A query like arnelgp posted should do the job. If it doesn't then you are missing something.

You missed that the column name needs to be parameterized. (Why, I don't know - that was just the OP requirement).

@PatAccess did you try the dynamic sql I posted?

 

[Isaac]

I just need it to say If the value in this field and this record ends with R then change its value by removing the R.

As many of us have posted, that would be as simple as something like:

update tablename set columnname = left(columnname,len(columnname)-1) where columnname like '%R'

But you stated you needed the column name to be dynamic (maybe you are feeding this to a stored procedure and you want the person to be able to input the column name, I'm not sure why), in which case, nothing will work but dynamic sql as I posted.

If you DON'T need the column name to be dynamic/parameterized, then why say so at the beginning?

Seems your requirements have changed a few times..

 

[PatAccess]

As many of us have posted, that would be as simple as something like:

update tablename set columnname = left(columnname,len(columnname)-1) where columnname like '%R'

But you stated you needed the column name to be dynamic (maybe you are feeding this to a stored procedure and you want the person to be able to input the column name, I'm not sure why), in which case, nothing will work but dynamic sql as I posted.

If you DON'T need the column name to be dynamic/parameterized, then why say so at the beginning?

Seems your requirements have changed a few times..

Yes I actually used an UPDATE SET with CASE WHEN in a stored procedure because I did need parameters and it worked. Thank you

 

[PatAccess]

I'll add an unimportant detail that will save you a lot of typing over time. dbo is the default schema and can be omitted.

When referring to another database too.

databasename.dbo.tablename.columnname

can be referred to as

databasename..tablename.columnname

Doesn't work for referring to functions though.

I didn't know you could use the 3rd part of replace that way REPLACE(REPLACE(field_name, 'POS', ''), 'R', '') so thanks for the tips

 

[PatAccess]

Sounds like you are trying to port inefficient VBA techniques you employed in Access to SQL Server. Learn the mantra "USE THE ENGINE".

You are complicating matters unnecessarily. A query like arnelgp posted should do the job. If it doesn't then you are missing something.

Yes moving from Access VBA to SQL is tricky but I am learning. Any tips? What do you mean by "USE THE ENGINE"?

 

[PatAccess]

I already run the Above query on a Test table in SQL server exress, and it does not put Null if the String is not found.
if the "field_name" has Either "POS" Or "R" on it it will be removed, if it is not found, nothing is altered.
no need to use CASE When...

UPDATE dbo.table_name SET field_name = REPLACE(REPLACE(field_name, 'POS', ''), 'R', '')

I used this with CASE WHEN for both POS and R since I didn't know that you could use it this way

 

[PatAccess]

Sorry, I was confused since this site is targeted at Access users. We do offer SQL support but generally it is SQL as a BE connected to an Access FE. We are always happy to help if we can but you might need to find a more targeted forum if your questions get too far afield

OK that's interesting. Yes I am more into full SQL Server now. No Access FE. Any suggestions?

 

[CJ_London]

Plenty of sql forums out there - just Google ‘sql forums’ or similar. Some free some you pay a membership. Some good, some not so. Some will be understanding of your current level of expertise, some not. Some you will get quick responses, some not at all.

you need to go explore and find one (or two) that work for you

 

[Galaxiom]

What do you mean by "USE THE ENGINE"?

Queries are the go because they use the database engine's ability to process the entire data set as a whole. Code with loops is generally far slower because it processes one record at a time. Using loops and cursors is a process derided by database engineers as RBAR, Row By Agonsing Row, spoken "rebar". (Maybe whoever came up with term was also a concreter )

TSQL does the job of both running queries and what Access does in VBA. It can do loops and cursors (the TSQL equivalent of a recordset) but the queries are usually faster unless you are processing very few records.

Some techniques to avoid RBAR, involve what are known as tally tables. It is a wide subject which you should familairise yourself with if you want to be a great developer. Here is a good example.

Say you have intermittent data but you need to process values for the days in between. The naive developer opens a recordset (cursor) and repeats the processing for the missing days. The knowledgeable developer generates a tally table of all the dates and outer joins it to the data in a query to process it all of it together.

Similar techniques of joining to a tally table are used to parse data instead of looping through it record by record.

The most important concept is that the engine processes whole sets of data at enormous speed. Avoid anything that loops and think of sets of data and you will be on the right track.

 

[Galaxiom]

Having learnt to database here, the move to SQL was just like going from walking to running.

I have not joined any SQL forums. It is a much bigger pond than Access so pretty much every question has been answered multiple times on multiple sites. I got good at using Google to answer all my questions.

The other thing I did was to periodically explore the SSMStudio and then Google to find out what the terms meant. So many times this led to information about features I could use immediately. When you see something mentioned in a query solution on a forum, Google it.

As an SQL beginner I can promise you that you could not possibly have any idea just where the journey into SQL will lead you. The scope of TSQL is mind blowing. I've been using it for ten years and I'm still learning. You will never regret making this leap.

 

[Galaxiom]

You missed that the column name needs to be parameterized.

Yep but that was not related to the point I was making. The OP seemed to be making it complicated.

BTW You might already know so please don't take it the wrong way.
Just saying, consider the alternative where you use EXEC @SQL for dynamic queries.

If they are run repeatedly, use the System Procedure sp_executesql.
It will store its query plan and can save significant resources and time over a simple EXEC of a command string.

 

[Minty]

I'd agree with @Galaxiom, on many of the points - you never stop learning T-SQL.
The best thing you can do is get your head into thinking in set-based terms, both in query design and data processing.

SQL Server is incredibly efficient at these things, and once you get used to it you'll never look back.

 

[Isaac]

Queries are the go because they use the database engine's ability to process the entire data set as a whole. Code with loops is generally far slower because it processes one record at a time. Using loops and cursors is a process derided by database engineers as RBAR, Row By Agonsing Row, spoken "rebar". (Maybe whoever came up with term was also a concreter )

TSQL does the job of both running queries and what Access does in VBA. It can do loops and cursors (the TSQL equivalent of a recordset) but the queries are usually faster unless you are processing very few records.

Some techniques to avoid RBAR, involve what are known as tally tables. It is a wide subject which you should familairise yourself with if you want to be a great developer. Here is a good example.

Say you have intermittent data but you need to process values for the days in between. The naive developer opens a recordset (cursor) and repeats the processing for the missing days. The knowledgeable developer generates a tally table of all the dates and outer joins it to the data in a query to process it all of it together.

Similar techniques of joining to a tally table are used to parse data instead of looping through it record by record.

The most important concept is that the engine processes whole sets of data at enormous speed. Avoid anything that loops and think of sets of data and you will be on the right track.

Think in terms of acting on Columns rather than Records. (Tough at first and may only make sense after you've already begun to do it)

 

[Isaac]

Yep but that was not related to the point I was making. The OP seemed to be making it complicated.

BTW You might already know so please don't take it the wrong way.
Just saying, consider the alternative where you use EXEC @SQL for dynamic queries.

If they are run repeatedly, use the System Procedure sp_executesql.
It will store its query plan and can save significant resources and time over a simple EXEC of a command string.

Thankfully I don't have to do it very often but I will remember that thank you. I usually only use it when executing a BCP command which is only once in awhile for a scheduled job. I will review those procedures to triple check to this, thanks.