How to Strip a Character Out of a Column in SQL Server

How do you strip a character out of a column in SQL Server?

This is done using the REPLACE function

To strip out "somestring" from "SomeColumn" in "SomeTable" in the SELECT query:

SELECT REPLACE([SomeColumn],'somestring','') AS [SomeColumn]  FROM [SomeTable]

To update the table and strip out "somestring" from "SomeColumn" in "SomeTable"

UPDATE [SomeTable] SET [SomeColumn] = REPLACE([SomeColumn], 'somestring', '')

how to remove the character '%' from string in sql

UPDATE TableName SET ColumnName = REPLACE(ColumnName,'%','')

Remove specific character from string (SQL Server 2012)

You can use replace() to do this. In this case it will search for 'B' and replace it with an empty string -> ''. Please note that this function will replace all 'B' from this column.

 SELECT 
       REPLACE([Port_Ticker], 'B', '') as "Fund"
      ,[BENCH_Ticker] as "Index ID"
      ,format(cast([VALUE_DATE] as Date),'dd/MM/yyyy') as "Updat"
      ,([Port_Risk_Contrib] / 10000000) as "StDev Fund"
      ,([Active_risk_contrib] / 10000000) as "TE"
      ,([BENCH_RISK_CONTRIB] / 100) as "StDev BM"
  FROM [DM_PORTFOLIO_ANALYSIS].[basedata].[PortfolioRiskFigures]
  where [BLOCK_FACTOR] = 'Total'
  and [Port_ticker] = 'B1023'
  order by [VALUE_DATE] asc

Remove certain characters from a string

You can use Replace function as;

REPLACE ('Your String with cityname here', 'cityname', 'xyz')
--Results
'Your String with xyz here'

If you apply this to a table column where stringColumnName, cityName both are columns of YourTable

SELECT REPLACE(stringColumnName, cityName, '')
FROM YourTable

Or if you want to remove 'cityName' string from out put of a column then

SELECT REPLACE(stringColumnName, 'cityName', '')
FROM yourTable

EDIT: Since you have given more details now, REPLACE function is not the best method to sort your problem. Following is another way of doing it. Also @MartinSmith has given a good answer. Now you have the choice to select again.

SELECT RIGHT (O.Ort, LEN(O.Ort) - LEN(C.CityName)-1) As WithoutCityName
FROM   tblOrtsteileGeo O
       JOIN dbo.Cities C
         ON C.foo = O.foo
WHERE  O.GKZ = '06440004'

How do I remove the first characters of a specific column in a table?

SELECT RIGHT(MyColumn, LEN(MyColumn) - 4) AS MyTrimmedColumn

Edit:
To explain, RIGHT takes 2 arguments - the string (or column) to operate on, and the number of characters to return (starting at the "right" side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves the leftmost 4 characters "behind".

Hope this makes sense.

Edit again - I just read Andrew's response, and he may very well have interperpereted correctly, and I might be mistaken. If this is the case (and you want to UPDATE the table rather than just return doctored results), you can do this:

UPDATE MyTable
SET MyColumn = RIGHT(MyColumn, LEN(MyColumn) - 4)

He's on the right track, but his solution will keep the 4 characters at the start of the string, rather than discarding said 4 characters.

How to clean/remove a column of Hidden characters or special characters in SQL Server?

If the format is YYYY-MM-DD, then try:

try_convert(int, replace(academic_year, '-', ''))

I'm a little confused why you think that removing spaces will affect the conversion.

how to remove characters from beginning and end of a column in sql

Using such an approach, you'd select substring that begins after the last slash / characters and ends before the next non-word (in this case, a dot . character):

SQL> with test (col) as
  2    (select 'T/PROD/Logs/somename.log' from dual)
  3  select
  4    regexp_substr(substr(col, instr(col, '/', -1) + 1), '\w+') result
  5  from test
  6  /

RESULT
--------
somename

SQL>

SQL Server replace, remove all after certain character

Use LEFT combined with CHARINDEX:

UPDATE MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

Note that the WHERE clause skips updating rows in which there is no semicolon.

Here is some code to verify the SQL above works:

declare @MyTable table ([id] int primary key clustered, MyText varchar(100))
insert into @MyTable ([id], MyText)
select 1, 'some text; some more text'
union all select 2, 'text again; even more text'
union all select 3, 'text without a semicolon'
union all select 4, null -- test NULLs
union all select 5, '' -- test empty string
union all select 6, 'test 3 semicolons; second part; third part;'
union all select 7, ';' -- test semicolon by itself    

UPDATE @MyTable
SET MyText = LEFT(MyText, CHARINDEX(';', MyText) - 1)
WHERE CHARINDEX(';', MyText) > 0

select * from @MyTable

I get the following results:

id MyText
-- -------------------------
1  some text
2  text again
3  text without a semicolon
4  NULL
5        (empty string)
6  test 3 semicolons
7        (empty string)

How to remove all special characters from a column name string using regular expressions

Try This:

DECLARE @str VARCHAR(400)='S$d#@gh'
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
SELECT @str

You can Create a SCALAR FUNCTION & pass this Column

CREATE FUNCTION dbo.Remove_SpecialCharacters( @str VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
RETURN @str
END

O/P

SELECT dbo.Remove_SpecialCharacters(COLUMNNAME),* FROM TABLENAME

---

Related Topics