Xjyuk

My perfect evil overlord plan... or is it?

Why did Missandei say this?

What does the "DS" in "DS-..." US visa application forms stand for?

Are wands in any sort of book going to be too much like Harry Potter?

How is Arya still alive?

TeX Gyre Pagella Math Integral sign much too small

And now you see it II (the B side)

What is a good way to allow only one non null field in an object

Locked my sa user out

A Latin text with dependency tree

resoldering copper waste pipe

Ugin's Conjurant vs. un-preventable damage

Names of the Six Tastes

Was the Highlands Ranch shooting the 115th mass shooting in the US in 2019

What can cause an unfrozen indoor copper drain pipe to crack?

What's an appropriate age to involve kids in life changing decisions?

I might have messed up in the 'Future Work' section of my thesis

Can I use a 11-23 11-speed shimano cassette with the RD-R8000 11-speed Ultegra Shadow Rear Derailleur (short cage)?

How to avoid making self and former employee look bad when reporting on fixing former employee's work?

Can I bring back Planetary Romance as a genre?

How does weapons training transfer to empty hand?

Best species to breed to intelligence

Company stopped paying my salary. What are my options?

Why are thrust reversers not used to slow down to taxi speeds?

Using SUBSTRING and RTRIM Together

Substring a result and renaming itTRIM() Function With Linked ServerConsolidating a row of data, based on previous rowsWhere should I put nvarchar(max) dimensions in my data warehouse?Is the 8KB row limit a “hard limit”?Unmarshalling Hierarchical JSON in SQL Server 2016+Substring without the first n charactersSql Substring and Search StringSUBSTRING and IF statementproblem with substring in mysql

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?
  
  – Jacob H
  5 hours ago
  

  
  
  
  

add a comment | 

1

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is there a delimiter between City and State like a comma? Or a space between? And is it consistent?
  
  – Jacob H
  5 hours ago
  

  
  
  
  

add a comment | 

I have a VARCHAR field that contains a city and a 2 digit state abbreviation, but there tends to be leading and trailing spaces in the field. I need to parse out the City and State Abbrev into separate fields. Currently I just have this to parse out the State, but I have no idea how to handle the City

RIGHT(RTRIM(alt.Address),2)

sql-server-2016 substring string-manipulation trim

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

RIGHT(RTRIM(alt.Address),2)

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

share|improve this question

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

edited 4 hours ago

Andriy M

16.5k63675

edited 4 hours ago

Andriy M

16.5k63675

asked 5 hours ago

Mike JonesMike Jones

626

asked 5 hours ago

Mike JonesMike Jones

626

1 Answer
1

active

oldest

votes

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(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 hours ago
  

  
  
  
  

 | 
show 2 more comments

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237688%2fusing-substring-and-rtrim-together%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 hours ago
  

  
  
  
  

 | 
show 2 more comments

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(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can make the second expression simpler: left(alt.Address, len(alt.Address) - 2). Apply ltrim and/or rtrim to the result of left if necessary. That works because len ignores trailing spaces.
  
  – Andriy M
  4 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks for sharing, I was not aware len() ignores the trailing spaces. However wouldn't you still need a ltrim() on the first parameter to left ? In the case of leading spaces.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  As I said, you can apply either ltrim or rtrim, or both, after applying the left, i.e. to the result of left.
  
  – Andriy M
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This actually still works with spaces in between city and state abbreviation, give it a shot.
  
  – kevinnwhat
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I took the liberty of adding the second option, as it's just a variation on your solution. If you don't like it, feel free to roll back, I don't mind, cheers
  
  – Andriy M
  4 hours ago
  

  
  
  
  

 | 
show 2 more comments

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(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

You can play with both options in this live demo at dbfiddle.uk.

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

select right(rtrim(alt.Address),2),
 rtrim(left(ltrim(alt.Address),len(ltrim(rtrim(alt.Address))) - 2))

select right(rtrim(alt.Address),2),
 ltrim(rtrim(left(alt.Address,len(alt.Address) - 2)))

share|improve this answer

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

edited 4 hours ago

Andriy M

16.5k63675

edited 4 hours ago

Andriy M

16.5k63675

answered 5 hours ago

kevinnwhatkevinnwhat

8716

answered 5 hours ago

kevinnwhatkevinnwhat

8716