Hot answers tagged


You could do that using the sql:column function and a Common Table Expression (CTE), something like this: CREATE TABLE #tmp ( col2 XML ) INSERT INTO #tmp ( col2 ) VALUES ( '<Root> <Row> <Rowid>trim this value </Rowid> </Row> </Root>' ) SELECT 'before' s, * FROM #tmp ;WITH cte AS ( SELECT col2, RTRIM( ...


simplest way - use REPLACE function select REPLACE('NES 123_4_5', ' ', '') Result - NES123_4_5 full list of string functions -


The 2nd parameter is the list of characters to trim, not a string as such. The manual about ltrim(string text [, characters text]): Remove the longest string containing only characters from characters (a space by default) from the start of string. So this: select ltrim('abc cab b ca foo', 'abc '); ... only leaves 'foo'. To actually remove one ...


Try this select right(rtrim(alt.Address),2), rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2)) A slight variation on the second expression (city), which works the same but uses fewer ltrim/rtrim calls (which makes it more concise, albeit not necessarily clearer; it depends on how explicit you prefer the logic to be): select right(rtrim(...


If you want to replace all spaces, use replace() replace('NES 123_4_5', ' ', '') If you only want to replace the space after the first three characters, you can use regexp_replace() regexp_replace('NES 123_4_5', '(^[A-Z]{3})( )', '\1') The regular expression will look for there (uppercase) characters at the beginning followed by a single space and will ...


Quotes must be escaped in the OPENQUERY argument. The return type for the TRIM function is text, so you should cast it back to varchar. Select * from OPENQUERY(LinkedServer, 'Select CAST(TRIM(trailing '' '' from ad.userid) AS varchar(8)) As "User ID" FROM alphadawg ad')

Only top voted, non community-wiki answers of a minimum length are eligible