Hot answers tagged

8

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( ...


5

simplest way - use REPLACE function select REPLACE('NES 123_4_5', ' ', '') Result - NES123_4_5 full list of string functions - https://www.postgresql.org/docs/9.6/static/functions-string.html


4

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 ...


3

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(...


1

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 ...


1

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