Solved Find characters and remove them (1 Viewer)

[PatAccess]

Hello all,

This is my first time using the SQL Server side of this forum after using the VBA side for over 6 years

I am working on a project and need help with a tsql script that will read a string
Most of the values are of the form 20R, 20POS, 200R or 200POS or 200.... and I want a script that will read the character and remove the R or POS if found
so something like

IF variable LIKE '%R%' or variable LIKE '%POS%'
BEGIN
    variablelength - 1?
END

Thank you,

 

[The_Doc_Man]

Perhaps you can use the REPLACE function?

SQL Server REPLACE Function By Practical Examples

In this tutorial, you will learn how to use the SQL Server REPLACE() function to replace all occurrences of a substring by a new substring within a string.

www.sqlservertutorial.net

Replace the "POS" part of the input string with a zero-length string.

 

[PatAccess]

Perhaps you can use the REPLACE function?

SQL Server REPLACE Function By Practical Examples

In this tutorial, you will learn how to use the SQL Server REPLACE() function to replace all occurrences of a substring by a new substring within a string.

www.sqlservertutorial.net

Replace the "POS" part of the input string with a zero-length string.

Ok I will try that but what metadata function can I use to return a field name. I have been using OBJECT_ID(DB_NAME) to return a database name.
Can I use OBJECT_NAME(FIELD_NAME) to return a field name. something like

IF OBJECT_NAME(field_name) LIKE '%R'
    BEGIN
        REPLACE(...)
    END

And will it replace the values in the table?

Thank you so much

 

[theDBguy]

...what metadata function can I use to return a field name.

Perhaps you could try this one.

COL_NAME (Transact-SQL) - SQL Server

COL_NAME (Transact-SQL)

docs.microsoft.com

 

[Isaac]

This sounds like it should be an update query, not a variable.

This is always preferable to a loop.

Have you tried this? Coming from VBA new into SQL makes us think "loop-y", but such is not usually called for (99% of the time)

 

[PatAccess]

This sounds like it should be an update query, not a variable.

This is always preferable to a loop.

Have you tried this? Coming from VBA new into SQL makes us think "loop-y", but such is not usually called for (99% of the time)

No I didn't try the update statement but wouldn't I need to still use the REPLACE function? Oooohh wait. Do you mean

UPDATE tbl_name
SET field_name = REPLACE(field_name,'R','')
WHERE field_name LIKE '%R'

But won't this add an empty character at the end?
Should I instead use

UPDATE tbl_name
SET field_name = RIGHT(field_name,1)
WHERE field_name LIKE '%R'

Since it will extract the last character?

 

[Isaac]

Yes, that's what I meant - your first code block.

Except I'm getting confused - can you state a clear requirement of what you're trying to replace?

 

[PatAccess]

Yes, that's what I meant - your first code block.

Except I'm getting confused - can you state a clear requirement of what you're trying to replace?

So I need the query to change an ID of the form 20R to 20 so it will remove the R at the end and update that value.
I just did an UPDATE and got it to work on a temp table BUT how do I use it on an if statement?

When an ID is chosen, IF that ID has R at the end so (LIKE '%R') then UPDATE that ID by removing the R

IF column_name LIKE '%R'
BEGIN
    UPDATE table SET column_name= REPLACE(.....)
    WHERE condition
END

but the problem is that it is not recognizing my column name in my query on the first line at IF ID LIKE '%R'. It gives me an "Invalid Column name" error

 

[PatAccess]

So far here is what I am trying to make work

BEGIN
    DECLARE @strID NVARCHAR = 'my_tbl_name.Column_name'
    
    IF @strID LIKE '%R'
        BEGIN
            UPDATE my_tbl_name
            SET Column_name = REPLACE(@strID,'R','')
        END
END

It runs successfully but when I go to check my table the ID is not changed.

What am I doing wrong?

 

[theDBguy]

What am I doing wrong?

I could be misinterpreting your goal, but I feel like this information might be useful for you.

COLUMNS (Transact-SQL) - SQL Server

COLUMNS (Transact-SQL)

docs.microsoft.com

 

[Isaac]

So far here is what I am trying to make work

BEGIN
    DECLARE @strID NVARCHAR = 'my_tbl_name.Column_name'
  
    IF @strID LIKE '%R'
        BEGIN
            UPDATE my_tbl_name
            SET Column_name = REPLACE(@strID,'R','')
        END
END

It runs successfully but when I go to check my table the ID is not changed.

What am I doing wrong?

If you want to parameterize the name of the column, you have no choice but to use dynamic SQL.
It will get a little ugly to figure out the syntax.

Something like (untested aircode sure to have mistakes)

declare @SQL nvarchar(max), @columnName varchar(255)

set @columnname = 'NameOfColumn'

set @SQL = 'update tablename set [' + @columnname + '] = replace([' + @columnname + '], + char(39) + 'R' + ',' + char(39) + char(39) + ')'

EXEC (@sql)

 

[The_Doc_Man]

I'm thinking maybe this simple:

            UPDATE my_tbl_name
            SET @strID = REPLACE(@strID,'R','')
            WHERE @strID LIKE '%R'

I'm no expert in SQL Server's ways, but it might be all you need. Unless I have misunderstood your intent.

 

[Isaac]

I'm thinking maybe this simple:

            UPDATE my_tbl_name
            SET @strID = REPLACE(@strID,'R','')
            WHERE @strID LIKE '%R'

I'm no expert in SQL Server's ways, but it might be all you need. Unless I have misunderstood your intent.

The engine would not understand "Set @strID" - at least, not in the context of the Update statement.

You cannot use a variable name in place of a column, it has to be dynamic sql

 

[Pat Hartman]

If you run the query in Access rather than in SQL Server, you can use the Val() function assuming what you want from the field are only the leading numeric characters.

 

[PatAccess]

If you run the query in Access rather than in SQL Server, you can use the Val() function assuming what you want from the field are only the leading numeric characters.

Unfortunately, I can only use SQL Server now. New Job I have to use TSQL...I just need to figure out how to better use IF in tsql, I think

 

[PatAccess]

If you want to parameterize the name of the column, you have no choice but to use dynamic SQL.
It will get a little ugly to figure out the syntax.

Something like (untested aircode sure to have mistakes)

declare @SQL nvarchar(max), @columnName varchar(255)

set @columnname = 'NameOfColumn'

set @SQL = 'update tablename set [' + @columnname + '] = replace([' + @columnname + '], + char(39) + 'R' + ',' + char(39) + char(39) + ')'

EXEC (@sql)

I'm going to try this and report back. I need to get better at using IF BEGIN END in TSQL...In Access VBA, I was able to do some if-then statements pretty easily but I have to learn how to do it with TSQL. 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.
I've been doing it incrementally via temp table and just using the UPDATE STATEMENT works do I need to figure out how to insert it into an IF BEGIN END statement

 

[Pat Hartman]

If you are using Access as the FE, you should be linking to the SQL Server tables and running queries in Access. Unless of course you bought into the lie that you can't use bound forms and Access querydefs.

Just FYI, Access attempts to make all queries "pass through" queries. It doesn't always succeed but that is its intent. Even using linked tables and bound forms, you might occasionally need to use actual pass through queries or stored procedures, but if you have built your application using good client/server techniques, Access works just fine as a FE. I have several BE's with millions of rows and I don't ever have to use T-SQL. I get to use querydefs. Sometimes I need to create views to optimize joins that happen frequently and sometimes I have a very complex report that needs a stored procedure to pull together a bunch of recordsets.

 

[PatAccess]

If you are using Access as the FE, you should be linking to the SQL Server tables and running queries in Access. Unless of course you bought into the lie that you can't use bound forms and Access querydefs.

Just FYI, Access attempts to make all queries "pass through" queries. It doesn't always succeed but that is its intent. Even using linked tables and bound forms, you might occasionally need to use actual pass through queries or stored procedures, but if you have built your application using good client/server techniques, Access works just fine as a FE. I have several BE's with millions of rows and I don't ever have to use T-SQL. I get to use querydefs. Sometimes I need to create views to optimize joins that happen frequently and sometimes I have a very complex report that needs a stored procedure to pull together a bunch of recordsets.

I used to use MS Access as an FE. I used it for almost 7 years and learned a lot but this new company I work for does not use Access. Solely SQL Server and other tools such as SSRS for reporting. so I am now deep in TSQL. I haven't really used it to the extent that I previously learned but I have to now.
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.

 

[arnelgp]

you can try this simple Update query:

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

 

[CJ_London]

Not sure I am following this correctly

It runs successfully but when I go to check my table the ID is not changed.

I presume strID is a) updateable and b) allows duplicates

BUT how do I use it on an if statement?

in TSql you use the CASE WHEN syntax instead of iif or if