Help in Sql query

Question

I am using sql compact database SDF

I need to select a column and remove all words after specific text found

for example:

Potatoes will expire in 2022 in inventory

when found ** remove everything

I want the output like below
Potatoes

Answer 1

It seems that your post has been victim to the fact that this forum uses markdown. The double asterisks are used to indicate bold. Thus, I will need take a guess on what you had originally. Also, I don't have access to SQL Server Compact, so this is only tested on regular SQL Server:

DECLARE @text varchar(50) = 'Potatoes ** will expire in 2022 in inventory'
DECLARE @specific varchar(20) = '**'
SELECT left(@text, charindex(@specific, @text) - 1)

You can use the button with ones and zeroes to insert code (which will evade the issue with the double asterisks being gobbled up), or select piece of text to press CTRL/K to the same effect. Use the Preview button to check the result before you submit.

Answer 2

Hi @Devos2022
We can use CHARINDEX and LEFT functions to get this.Please refer to these documents for more details: LEFT (Transact-SQL) , CHARINDEX (Transact-SQL)
CHARINDEX(‘b’, ‘abc’)：Returns the position of b in 'abc'；
CHARINDEX(‘b’, ‘abc’)-1 ：Position the position to the character before b; in this case, it can be understood as the length of the string before b；
LEFT(‘abc’ ,CHARINDEX (‘b’ ,‘abc’)-1 ) ：Take the length of the string before b from left to right in abc；
When the specified character cannot be found in the expression, the return value of CHARINDEX('d', 'abc')-1 is '-1'.However, in the LEFT function, the following integer_expression cannot be a negative value. In this case , you can add a judgment condition to the SQL, when the specified character cannot be found, the original expression will be returned.

Please check this sample ：

create table #test(id INT,val varchar(30));  
insert into #test values(1,'asdfgu'),(2,'qwert'),(3,'yuhfgp'),(4,'xcvfsbnm');  
SELECT id,LEFT(val,IIF(CHARINDEX('f', val)-1<0,LEN(val),CHARINDEX('f', val)- 1))  
FROM #test  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you guys