Solved Find characters and remove them

P

PatAccess

Registered User.
Local time
Today, 08:27
Joined
May 24, 2017
Messages
284
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
Code: 
IF variable LIKE '%R%' or variable LIKE '%POS%'
BEGIN
    variablelength - 1?
END

Thank you,
 
The_Doc_Man said:
Perhaps you can use the REPLACE function?

www.sqlservertutorial.net

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 www.sqlservertutorial.net

Replace the "POS" part of the input string with a zero-length string.
Click to expand...
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
Code: 
IF OBJECT_NAME(field_name) LIKE '%R'
    BEGIN
        REPLACE(...)
    END
And will it replace the values in the table?

Thank you so much
 
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)
 
Isaac said:
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)
Click to expand...
No I didn't try the update statement but wouldn't I need to still use the REPLACE function? Oooohh wait. Do you mean
Code: 
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
Code: 
UPDATE tbl_name
SET field_name = RIGHT(field_name,1)
WHERE field_name LIKE '%R'
Since it will extract the last character?
 
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?
 
Isaac said:
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?
Click to expand...
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

Code: 
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
 
So far here is what I am trying to make work
SQL: 
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?
 
PatAccess said:
So far here is what I am trying to make work
SQL: 
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?
Click to expand...

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)

Code: 
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 thinking maybe this simple:

Code: 
            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_Doc_Man said:
I'm thinking maybe this simple:

Code: 
            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.
Click to expand...
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
 
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.
 
Pat Hartman said:
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.
Click to expand...
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
 
Isaac said:
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)

Code: 
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)
Click to expand...
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
 
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.
 
Pat Hartman said:
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.
Click to expand...
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.
 
you can try this simple Update query:

UPDATE dbo.table_name SET field_name = REPLACE(REPLACE(field_name, 'POS', ''), 'R', '')
 
Not sure I am following this correctly
It runs successfully but when I go to check my table the ID is not changed.
Click to expand...
I presume strID is a) updateable and b) allows duplicates

BUT how do I use it on an if statement?
Click to expand...
in TSql you use the CASE WHEN syntax instead of iif or if
 
You must log in or register to reply here.

Similar threads

G
Upsized Back End to SQL, does Caching need to be removed?
Replies
13
Views
541
Pat Hartman
P
D
Solved Format a 10 digit character text box using Access VBA
Replies
19
Views
634
Mark_
M
I
SQL Server importing strange characters despite specifying nvarchar
Replies
10
Views
1,156
ions
I
M
Data Macro - Strange Performance Issue
2
Replies
32
Views
850
mellamokb
M
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
464
ricardocpa
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom