Remove all spaces from a string in SQL Server

[Originally Posted By]: http://stackoverflow.com/questions/10432086/remove-all-spaces-from-a-string-in-sql-server

What is the best way to remove all spaces from a string in SQL Server 2008?

LTRIM(RTRIM(‘ a b ‘) would remove all spaces at the right and left of the string, but I also need to remove the space in the middle.

shareedit

Simply replace it;

SELECT REPLACE(fld_or_variable, ' ', '')

Edit Just to clarify; its a global replace, there is no need to trim() or worry about multiple spaces:

create table #t (c char(8), v varchar(8))
insert #t (c, v) values 
    ('a a'    , 'a a'    ),
    ('a a  '  , 'a a  '  ),
    ('  a a'  , '  a a'  ),
    ('  a a  ', '  a a  ')

select
    '''' + c + '''' [IN], '''' + replace(c, ' ', '') + '''' [OUT]
from #t  
union all select
    '''' + v + '''', '''' + replace(v, ' ', '') + ''''
from #t 

IN            OUT
'a a     '   'aa'
'a a     '   'aa'
'  a a   '   'aa'
'  a a   '   'aa'
'a a'        'aa'
'a a  '      'aa'
'  a a'      'aa'
'  a a  '    'aa'
shareedit
Advertisements

SQL query with “not exists” not working

[Originally Posted By]: http://stackoverflow.com/questions/18904778/sql-query-with-not-exists-not-working

I’m trying to use a simple query but I’m not getting anywhere. The objective is to “learn” how “not exists” works. I have a simple table with “idUsuario” as default ID for users and a name.

    SELECT * FROM usuario
    WHERE NOT EXISTS (
        SELECT * FROM usuario
        WHERE u.idUsuario =16
    )

Here i’m trying to get ALL the users from the table where the ID IS NOT 16. But it just get all of them.. What am I doing wrong?

Thanks in advance!

shareedit

You should join result from your query with result from the subquery as below

 SELECT * FROM usuario u
    WHERE NOT EXISTS (
        SELECT * FROM usuario u1
        WHERE u1.idUsuario = u.idUsuario
        and u1.idUsuario =16
    )

SQL Fiddle DEMO

shareedit

Parse comma-separated string to make IN List of strings in the Where clause

[Originally Posted By]: http://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause

My stored procedure receives a parameter which is a comma-separated string:

DECLARE @Account AS VARCHAR(200)
SET @Account = 'SA,A'

I need to make from it this statement:

WHERE Account IN ('SA', 'A')

What is the best practice for doing this?

shareedit

Create this function (sqlserver 2005+)

CREATE function [dbo].[f_split]
(
@param nvarchar(max), 
@delimiter char(1)
)
returns @t table (val nvarchar(max), seq int)
as
begin
set @param += @delimiter

;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @t
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end

use this statement

SELECT *
FROM yourtable 
WHERE account in (SELECT val FROM dbo.f_split(@account, ','))

Comparing my split function to XML split:

Testdata:

select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a 
from master..spt_values a cross join master..spt_values b

XML:

 SELECT count(t.c.value('.', 'VARCHAR(20)'))
 FROM (
     SELECT top 100000 x = CAST('<t>' + 
           REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)
           from a
 ) a
 CROSS APPLY x.nodes('/t') t(c)
Elapsed time: 1:21 seconds

f_split:

select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')
Elapsed time: 43 seconds

This will change from run to run, but you get the idea

shareedit

How do I identify if a string is a number?

[Originally Posted By]: http://stackoverflow.com/questions/894263/how-do-i-identify-if-a-string-is-a-number

If I have these strings:

  1. "abc" = false
  2. "123" = true
  3. "ab2" = false

Is there a command, like IsNumeric or something else, that can identify if a string is a valid number?

shareedit
int n;
bool isNumeric = int.TryParse("123", out n);
shareedit

This will return true if input is all numbers. Don’t know if it’s any better than TryParse, but it will work.

Regex.IsMatch(input, @"^\d+$")

If you just want to know if it has one or more numbers mixed in with characters, leave off the ^ +and $.

Regex.IsMatch(input, @"\d")

Edit: Actually I think it is better than TryParse because a very long string could potentially overflow TryParse.

shareedit

How to change 1 char in the string?

[Originally Posted By]: http://stackoverflow.com/questions/8987141/how-to-change-1-char-in-the-string

I have a next code:

string str = "valta is the best place in the World";

And i need to replace the first symbol, i do so:

str[0] = 'M';

But I recieve an error. How can I do this?

shareedit

Strings are immutable, meaning you can’t change a character. Instead, you create new strings.

What you are asking can be done several ways. The most appropriate solution will vary depending on the nature of the changes you are making to the original string. Are you changing only one character? Do you need to insert/delete/append?

Here are a couple ways to create a new string from an existing string, but having a different first character:

str = 'M' + str.Remove(0, 1);

str = 'M' + str.Substring(1);

Above, the new string is assigned to the original variable, str.

I’d like to add that the answers from others demonstrating StringBuilder are also very appropriate. I wouldn’t instantiate a StringBuilder to change one character, but if many changes are needed StringBuilder is a better solution than my examples which create a temporary new string in the process. StringBuilder provides a mutable object that allows many changes and/or append operations. Once you are done making changes, an immutable string is created from the StringBuilder with the .ToString() method. You can continue to make changes on the StringBuilder object and create more new strings, as needed, using .ToString().

shareedit

Optional parameters in ASP.NET Web API

[Originally Posted By]: http://stackoverflow.com/questions/11862069/optional-parameters-in-asp-net-web-api

I need to implement the following WebAPI method:

/api/books?author=XXX&title=XXX&isbn=XXX&somethingelse=XXX&date=XXX

All of the parameters can be null, i.e. the caller can specify from 0 to all the 5 parameters.

In MVC4 beta I used to do the following:

public class BooksController : ApiController
{
    // GET /api/books?author=tolk&title=lord&isbn=91&somethingelse=ABC&date=1970-01-01
    public string GetFindBooks(string author, string title, string isbn, string somethingelse, DateTime? date) 
    {
        // ...
    }
}

MVC4 RC doesn’t behave like this anymore. If I specify less than 5 parameters, it replies with a 404saying

No action was found on the controller ‘Books’ that matches the request.

What is the correct method signature to make it behave like it used to, without having to specify the optional parameter in the URL routing?

shareedit

This issue has been fixed in the regular release of MVC4. Now you can do:

public string GetFindBooks(string author="", string title="", string isbn="", string  somethingelse="", DateTime? date= null) 
{
    // ...
}

and everything will work out of the box.

shareedit

Find “n” consecutive free numbers from table

[Originally Posted By]: http://dba.stackexchange.com/questions/36943/find-n-consecutive-free-numbers-from-table

I have some table with numbers like this (status is either FREE or ASSIGNED)

id_set  number  status         
-----------------------
1       000001  ASSIGNED
1       000002  FREE
1       000003  ASSIGNED
1       000004  FREE
1       000005  FREE
1       000006  ASSIGNED
1       000007  ASSIGNED
1       000008  FREE
1       000009  FREE
1       000010  FREE
1       000011  ASSIGNED
1       000012  ASSIGNED
1       000013  ASSIGNED
1       000014  FREE
1       000015  ASSIGNED

and I need to find “n” consecutive numbers, so for n = 3, query would return

1       000008  FREE
1       000009  FREE
1       000010  FREE

It should return only first possible group of each id_set (in fact, it would be executed only for id_set per query)

I was checking WINDOW functions, tried some queries like COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), but that’s all I got 🙂 I couldn’t think of logic, how to do that in Postgres.

I was thinking about creating virtual column using WINDOW functions counting preceding rows for every number where status = ‘FREE’, then select first number, where count is equal to my “n” number.

Or maybe group numbers by status, but only from one ASSIGNED to another ASSIGNED and select only groups containing at least “n” numbers

EDIT

I found this query (and changed it a little bit)

WITH q AS
(
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
         ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,
       MIN(number) AS first_number,
       MAX(number) AS last_number,
       status,
       COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
         rnd - rn,
         status
ORDER BY
     first_number

which produces groups of FREE/ASSIGNED numbers, but I would like to have all numbers from only first group which meets the condition

SQL Fiddle

shareimprove this question

This is a problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here’s a SQL Fiddle demo* link for this query: http://sqlfiddle.com/#!1/a2633/1.

UPDATE

To return only one set, you could add in one more round of ranking:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
),
ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY id_set, grp) AS rnk
  FROM counted
  WHERE cnt >= 3
)
SELECT
  id_set,
  number
FROM ranked
WHERE rnk = 1
;

Here’s a demo for this one too: http://sqlfiddle.com/#!1/a2633/2.

If you ever need to make it one set per id_set, change the RANK() call like this:

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

Additionally, you could make the query return the smallest matching set (i.e. first try to return the first set of exactly three consecutive numbers if it exists, otherwise four, five etc.), like this:

RANK() OVER (ORDER BY cnt, id_set, grp) AS rnk

or like this (one per id_set):

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

* The SQL Fiddle demos linked in this answer use the 9.1.8 instance as the 9.2.1 one doesn’t appear to be working at the moment.

shareimprove this answer