StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would recommend a combination of PatIndex and Left. Carefully constructed, you can write a query that always works, no matter what your data looks like.</p> <p>Ex:</p> <pre><code>Declare @Temp Table(Data VarChar(20)) Insert Into @Temp Values('BTA200') Insert Into @Temp Values('BTA50') Insert Into @Temp Values('BTA030') Insert Into @Temp Values('BTA') Insert Into @Temp Values('123') Insert Into @Temp Values('X999') Select Data, Left(Data, PatIndex('%[0-9]%', Data + '1') - 1) From @Temp </code></pre> <p>PatIndex will look for the first character that falls in the range of 0-9, and return it's character position, which you can use with the LEFT function to extract the correct data. Note that PatIndex is actually using Data + '1'. This protects us from data where there are no numbers found. If there are no numbers, PatIndex would return 0. In this case, the LEFT function would error because we are using Left(Data, PatIndex - 1). When PatIndex returns 0, we would end up with Left(Data, -1) which returns an error.</p> <p>There are still ways this can fail. For a full explanation, I encourage you to read:</p> <p><a href="http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server" rel="noreferrer">Extracting numbers with SQL Server</a></p> <p>That article shows how to get numbers out of a string. In your case, you want to get alpha characters instead. However, the process is similar enough that you can probably learn something useful out of it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload