Sql Query - How to Trim this?

Question

What is the SQL Server 2K5 query to trim off the "# - " from the beginning of my varchar field:
For example:
211 - My PC
13 - Work 2

Should be converted to:
My PC
Work 2

I will then ORDER BY the results to have a better sorted resultset.

Thanks.

Answer 1

Unfortunately, Transact-SQL has limited support for regular expressions.

Fortunately, you have a clear pattern: you want to extract all text following the dash-space.

So, we do a substring from the location where the dash-space exists, to the end of the column, select that as trimmed_column, and order the results by that alias.

SELECT SUBSTRING(column, PATINDEX('%- '%', column) + 2, 150) AS trimmed_column FROM table ORDER BY trimmed_column

Note that the third argument of SUBSTRING is the number of characters to return; set it to be larger than the number of characters you expect will be returned after evaluation.

UPDATE:

A later answer claims you can use InStr in SQL Server or Access. There is no InStr function in Transact-SQL.

That said, RIGHT is another way to extract the wanted characters:

SELECT RIGHT(column, (LEN(column) - PATINDEX('%- '%', column))) AS trimmed_column FROM table ORDER BY trimmed_column

Answer 2

Trim Sql Query

Answer 3

Your question has no indication by connect or the place as to how the three tables are appropriate. besides, there is no reason to have a set by while you're actually not having some variety of columnar functionality interior the resultset. Assuming that 'shares' is the relational table between 'customer' and ' preserving': go with cname, sname, cprice FROM shares s connect preserving h ON s.holdingID = h.holdingID connect customer c ON s.clientID = c.clientID the place scat = 'leis/hotel' or go with cname, sname, cprice FROM shares s, preserving h, customer c the place scat = 'leis/hotel' AND s.holdingID = h.holdingID AND s.clientID = c.clientID If there are no direct fields in those tables that are foriegn keys to a minimum of between the others, there must be another table(s) that DO supply the linkage by some ability. otherwise, you will in basic terms get the Cartestian manufactured from all the conceivable mixtures of the three table's rows.

Answer 4

What is SQL TRIM?
The SQL Trim feature is are functions (LTRIM and RTRIM) that remove leading and trailing blanks from a string.

For example, the function LTRIM(' Minnesota') returns:

'Minnesota'


Why Use SQL TRIM?
The SQL Trim functions (LTRIM and RTRIM) are useful in cleansing data that contains leading and trailing blanks.

How To Use SQL TRIM
SQL TRIM is used as follows.

SQL TRIM Syntax


SELECT LTRIM()


SELECT RTRIM()




SQL TRIM Example

The LTRIM function removes the leading blanks in the string ' Bob'.

This SQL Statement with LTRIM is executed:


SELECT LTRIM(' Bob ') as trimmed_string



Here is the result.

trimmed_string
Bob


Or use LEFT() or RIGHT() in the SQL command

Answer 5

Here ya go:
SELECT LTRIM(RIGHT(test_fld, Len(test_fld) - InStr(test_fld, '-'))) AS test_fld_nonum
FROM MyTable

Of course, 'test_fld' is the field, and 'MyTable' is your table. :)

And FWIW, this select statement should work on MS Access, MySQL, or of course SQL Server.

Answer 6

This link has UDF which will help you to trim the database.
http://blog.sqlauthority.com/2007/04/24/sql-server-trim-function-udf-trim/