SQL Server Replace, Remove All After Certain Character

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 trim everything after certain character in sql

SELECT REPLACE(LEFT(Email, CHARINDEX('@',Email)-1),'_',' ')
FROM [DSR].[dbo].[RCA_Dashboard]

Deleting everything after a certain word in a SQL string

You can use CHARINDEX to find the position of a string inside another string:

CHARINDEX('BLDG', ColumnName)

Then use LEFT to only take everything up to that point:

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName))
FROM Table

And finally, since you want to include the BLDG text, you need to add 3 to the position (i.e. length of the string-1):

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName)+3)
FROM Table

If you want to also delete the BLDG word, then subtract 1 instead:

SELECT LEFT(ColumnName, CHARINDEX('BLDG', ColumnName)-1)
FROM Table

Remove everything after a specific special character in SQL Server

Think you're almost there - you just need to filter to ensure you aren't passing a negative number to the LEFT
e.g.,

SELECT left(name, charindex('|',name) -1) from table1
WHERE name LIKE '%|%'

Remember charindex is 0 if it doesn't exist in string, then you subtract 1. This means you're trying to do a LEFT(name, -1) which is invalid.

To get your full output (with all rows, regardless of the | symbol) you can use it in a CASE instead.

SELECT CASE 
        WHEN [name] LIKE '%|%' THEN left(name, charindex('|',name) -1) 
        ELSE [name] END AS [name]
from table1

Edit: Here's a db<>fiddle with results.

Removing all but one of a certain character in a string

Use replace,substring and len function

select replace(substring(@x,0,len(@x) - 3),'.','') + substring(@x,len(@x) - 3,len(@x))

EDIT:
If the name extension has a variable length, you can use the following query

select 
   CONCAT(
     replace(substring(@x,0,len(@x) - CHARINDEX('.',TRIM(REVERSE(@x)))),'.','')
   , 
     substring(@x,len(@x) - CHARINDEX('.',TRIM(REVERSE(@x))),len(@x))
   )

Result

remove substring after certain character if there is no number in it

This seems to do what you want; get the position of the last dot, check if those characters contain a number and if they do return Name. If not, string those characters from the end of the string:

SELECT *,
       CASE WHEN RIGHT(V.[Name],CI.LastDot) LIKE '%[0-9]%' THEN V.Name ELSE LEFT(V.[Name], LEN(V.Name) - CI.LastDot) END
FROM (VALUES(1,'example.jpg'),
            (2,'exampleexample01.01.2014'),
            (3,'example'),
            (4,'example1.pdf'),
            (5,'example13.pdf'))V(ID,Name)
     CROSS APPLY(VALUES(CHARINDEX('.',REVERSE(V.Name))))CI(LastDot);

Get everything after and before certain character in SQL Server

If you want to get this out of your table using SQL, take a look at the following functions that will help you: SUBSTRING and CHARINDEX. You can use those to trim your entries.

A possible query will look like this (where col is the name of the column that contains your image directories:

SELECT SUBSTRING(col, LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) + 1, 
    LEN(col) - LEN(SUBSTRING(col, 0, LEN(col) - CHARINDEX ('/', col))) - LEN(SUBSTRING(
    col, CHARINDEX ('.', col), LEN(col))));

Bit of an ugly beast. It also depends on the standard format of 'dir/name.ext'.

Edit:

This one (inspired by praveen) is more generic and deals with extensions of different length:

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX ('/', col))) + 1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX ('/', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('.', col))) - 1);

How to remove everything before a certain character in SQL Server?

Try this:

right(MyColumn, len(MyColumn) - charindex('-', MyColumn))

Charindex finds the location of the hyphen, len finds the length of the whole string, and right returns the specified number of characters from the right of the string.

Remove text after character (cut string sql)

General rule:

select RIGHT(fieldName,len(fieldName) - patindex('%#%',fieldName))

Examples:

select RIGHT('12341#123',len('12341#123') - patindex('%#%','12341#123'))

select RIGHT('123#1',len('123#1') - patindex('%#%','123#1'))

---

Related Topics