SQL QUERY (SELECT Characters)

[mari_outtaleb22]

Hello,

HOW I CAN ELIMINATE THE SYMBOLS AND KEEP ONLY THE NUMBERS AND LETTERS IN THE RESULT OF MY QUERY SQL?

FOR EXAMPLE example :

From : ML TUBE INOX 316L Ø 27X3MM""

To : ML TUBE INOX 316L 27X3MM

Any ideas?

Regards.

[SonTran · Accepted solution]

Hi,

In your case, follow these steps:

1. Create function

Create FUNCTION dbo.UF_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

2. Then query with the function

[mari_outtaleb22]

Hi,

The last solution worked for me,

thank you all.

[mari_outtaleb22]

Hi clas.hortien,

My version SQL Server is 2012.

sontran, I executed the query but it displays this error message.

Regards.

DeCLARE @str VARCHAR(400)= (select Itemcode from OITM)
DeCLARE @expres VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,Ø,",!]%'

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

 A.ITEMCODE, A.ItemName
,A.[DfltWH] , A.OnHand 'QTY', 
case when B.UomCode='Unité' THEN 'U'  when B.UomCode='PQt' then 'P'
 WHEN B.UomCode='Kilowatt' then 'KW' WHEN a.UgpEntry='10' then 'L'
 when A.UgpEntry='13' THEN 'KW' WHEN A.UgpEntry='11' THEN 'M²'
  ELSE B.UomCode
 END AS 'Unite de MSR', 
 A.LstEvlPric 
FROM OITM A
LEFT JOIN OUOM B ON A.[UgpEntry] = B.[UomEntry]

WHERE A.ItemCode LIKE 'MT%' AND  A.frozenFor ='N' AND ONHAND>0

Error message :
Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.(809 row(s) affected)

[SonTran]

Hi,

You can refer this topic for the same

Hope this helps,

Son Tran

[clas_hortien]

Hi,

if you have SQL Server 2017 or higher you can use the translate function from here.

"Returns the string provided as a first argument, after some characters specified in the second argument are translated into a destination set of characters, specified in the third argument."

Nice example can be found here.

Regards

Clas

[mari_outtaleb22]

HI clas.hortien,

thanks for the answer, but this solution just if I have one character to replace but I have a lot of characters in my table.

For example :

RACCORD INOX UNION A/S 1"

PLAQUE A BUSE 399422 60µ

SOND PT100 Ø 6MM AVEC CABLE 3FILS

Regards.

[clas_hortien]

Hello,

this can be done with the replace function like so:

select replace('ML TUBE INOX 316L Ø 27X3MM""','Ø','')

This has to be done for each symbol you want to replace.

Regards

Clas