|
|
Posted by Erland Sommarskog on 06/13/04 11:27
Radu (cuca_macaii2000@yahoo.com) writes:
> Hi. I have data which comes as a string like
>
> "... Store #61"
> but sometimes it is
>
> "... Store 61"
> and sometimes it is
>
> "... Store 061"
>
> For three digits, it might be "... Store 561" or "... Store #561", or
> "... Store 0561".....
>
> The only thing I can be sure of is that the last 2 or 3 (significant)
> digits of this field represent the StoreNumber.
>
> I have to link this table on field StoreNumber with another table where
> the data is ALWAYS like 0061, 0561, etc, so always four digits, padded
> with zeroes.
>
> I'd like to use the equivalent of the VB function
> Format(StoreNumber), "0000"), but Format does NOT exist in TSQL.
>
> How could I solve this problem ? Please bear with me - I'm a beginner
> in SQL...
Denis suggested stuff(), but that will not take you far. T-SQL is
fairly poor on string manipulation, but with some creativity you can
do quite a bit. Here is one for you:
DECLARE @x varchar(20)
SELECT @x = 'Store #61'
SELECT @x = right(replicate('0', 5) +
right(@x, patindex('%[^0-9]%', reverse(@x)) - 1), 4)
SELECT @x
Parindex is use to locate the last non-digit in the string, and we
use reverse to look at the string backwards. right() picks the specified
characters at the end of the string.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|